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

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
Joined
Apr 11, 2010
Messages
11,005
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>
 

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
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>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,493
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
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.
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
Top