Lookup up column and row header matches and return corresponding data

[pacerfan07]

I have a table on sheet1 that looks like the table below. On sheet2 cell A1 is "ATL" and cell B1 is "Yellow" How to I matchup the sheet2 values with sheet1 so that the formula would return "40" based on this example? Thanks!

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Orange[/TD]
[TD]Yellow[/TD]
[TD]Black[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ATL[/TD]
[TD]60[/TD]
[TD]40[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]MIA[/TD]
[TD]20[/TD]
[TD]50[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BKN[/TD]
[TD]70[/TD]
[TD]30[/TD]
[TD]40[/TD]
[/TR]
</tbody>[/TABLE]

 

[Little_Clubber]

Hi pacerfan07, this is the formulae you will need:

=HLOOKUP(B1,Sheet1!B:Z,MATCH(A1,Sheet1!A:A,0),FALSE)

 

[FDibbins]

While the suggestion in post #3 will work, I would advise against using full-column references with v/hlooup, it could slow your file down. Below is a more efficient method...
Data sheet6
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td]Orange[/td][td]Yellow[/td][td]Black[/td][/tr]

[tr][td]

2​

[/td][td]ATL[/td][td]60[/td][td]40[/td][td]30[/td][/tr]

[tr][td]

3​

[/td][td]MIA[/td][td]20[/td][td]50[/td][td]80[/td][/tr]

[tr][td]

4​

[/td][td]BKN[/td][td]70[/td][td]30[/td][td]40[/td][/tr]
[/table]

extract sheet...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]ATL[/td][td]

40​

[/td][/tr]

[tr][td]

2​

[/td][td]Yellow[/td][td][/td][/tr]
[/table]
B1=INDEX(Sheet6!$B$2:$D$4,MATCH($A$1,Sheet6!$A$2:$A$4,0),MATCH($A$2,Sheet6!$B$1:$D$1,0))

 

[Little_Clubber]

Yellow should be in B2.

I referenced the full columns etc so that it allows the table to grow without need to change the formulae.
I like your method too though

 

[FDibbins]

Yellow should be in B2.

I referenced the full columns etc so that it allows the table to grow without need to change the formulae.
I like your method too though

1. Based on the sample above, column B is Orange, not yellow
2. You can still use a defined range, using something like 2-3 times more than you think you would need. Even a range of 5000 rows is still better than 1 million+ rows that full-column references would use. Also, INDEX/MATCH is more efficient than H/VLOOKUP.

If you want the formula to grow as the data range increases, maybe consider using a Structured Table for the data

3. Thanks for the input

 

[mrshl9898]

1.

On sheet2 cell A1 is "ATL" and cell B1 is "Yellow"

 

[FDibbins]

1.

OK that is on the extract sheet. I was referring to what I suggested in my post. If necessary, adjust the references as needed

 

[Little_Clubber]

1.

That's what I meant. Sheet 2, Yellow is in B1, not in A2... but then I said B2 in error.

 

[pacerfan07]

Thanks everyone both of these methods got the job done.