# Get Column2 for the following list of Column1 when the column position change

#### dablue

##### New Member
For example:

 SKU Ranking 1 111 2 222 3 333 4 444 5 555

<tbody>
</tbody>

How do I extract the value of "ranking" given its equivalent SKU with a formula where if the column position changes it would still work? Say form a new table such that:

 SKU Ranking 2 222 4 444

<tbody>
</tbody>

I have tried various INDEX/MATCH combinations without success. VLOOKUP is not an option since column position may change. Thank you.

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### oldbrewer

##### Well-known Member
 SKU Ranking Sales Profit 1 111 500 115 2 222 600 135 last months order of columns 3 333 700 155 4 444 800 175 5 555 900 195 col E row 14 SKU Sales Ranking Profit 4 500 333 115 3 600 111 135 this months new rankings with 2 700 444 155 ranking in different column 5 800 222 175 1 900 555 195 SKU Ranking row 23 3 111 formula giving sku 3 5 222 4 333 =OFFSET(OFFSET(\$A\$14,0,MATCH("Ranking",\$B\$14:\$E\$14,0)),MATCH(B24,\$D\$15:\$D419,0),-2) 2 444 1 555

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>

#### oldbrewer

##### Well-known Member
typo corrected in formula

 SKU Ranking Sales Profit 1 111 500 115 2 222 600 135 last months order of columns 3 333 700 155 4 444 800 175 5 555 900 195 col E row 14 SKU Sales Ranking Profit 4 500 333 115 3 600 111 135 this months new rankings with 2 700 444 155 ranking in different column 5 800 222 175 1 900 555 195 SKU Ranking row 23 3 111 formula giving sku 3 5 222 4 333 =OFFSET(OFFSET(\$A\$14,0,MATCH("Ranking",\$B\$14:\$E\$14,0)),MATCH(B24,\$D\$15:\$D\$19,0),-2) 2 444 1 555

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>

#### Peter_SSs

##### MrExcel MVP, Moderator
Actually VLOOKUP could be used but I have chosen Index/Match. I would certainly avoid the volatile OFFSET if possible.

Try something like this, copied down.

Excel Workbook
ABCDEFGH
1SKURanking
21111
32222
43333
54444
65555
7
8
9
10SKURanking
112222
124444
131111
dablue

Replies
5
Views
185
Replies
4
Views
146
Replies
1
Views
2K
Replies
6
Views
1K
Replies
2
Views
588

1,182,107
Messages
5,933,697
Members
436,905
Latest member
Ibraeh

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back