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

dablue

New Member
Joined
Aug 5, 2017
Messages
1
For example:

SKURanking
1111
2222
3333
4444
5555

<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:

SKURanking
2222
4444

<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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
SKURankingSalesProfit
1111500115
2222600135last months order of columns
3333700155
4444800175
5555900195
col E
row 14SKUSalesRankingProfit
4500333115
3600111135this months new rankings with
2700444155ranking in different column
5800222175
1900555195
SKURankingrow 23
3111formula giving sku 3
5222
4333=OFFSET(OFFSET($A$14,0,MATCH("Ranking",$B$14:$E$14,0)),MATCH(B24,$D$15:$D419,0),-2)
2444
1555

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
typo corrected in formula

SKURankingSalesProfit
1111500115
2222600135last months order of columns
3333700155
4444800175
5555900195
col E
row 14SKUSalesRankingProfit
4500333115
3600111135this months new rankings with
2700444155ranking in different column
5800222175
1900555195
SKURankingrow 23
3111formula giving sku 3
5222
4333=OFFSET(OFFSET($A$14,0,MATCH("Ranking",$B$14:$E$14,0)),MATCH(B24,$D$15:$D$19,0),-2)
2444
1555

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top