Lookup across multiple columns and row to produce a single result

[Tomitsch]

I am try to lookup Regions for each state in a vertical list. The problem is that the lookup table has several states in each reagion. So, in my lookup column I have OH (Ohio) I want to get the result region LRD or NE (Nebraska) = NWD, etc. Yes, I could destruct the table to make a linear list so each state has a matching value. But it seems I should be able to do this across a range of cells. I have tried all sorts of index/match, vlookups, hlookups etc.

I guess I cannot attach files at this time....being I am new on the site and all

[TABLE="width: 672"]
<colgroup><col style="width: 48pt;" span="14" width="64"> <tbody>[TR]
[TD="class: xl69, width: 64, bgcolor: #FFE699"]Lookup Table[/TD]
[TD="class: xl69, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl69, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl69, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl69, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl69, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl69, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl69, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl69, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl69, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl69, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl69, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl69, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl69, width: 64, bgcolor: #FFE699"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #548235"]Region[/TD]
[TD="class: xl67, bgcolor: #305496, colspan: 13"]States[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]LRD[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]WV[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]KY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]TN[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]IN[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]OH[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]MI[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]MVD[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]MS[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]IL[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]MN[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]WI[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]LA[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]IA[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]ND[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]NAD[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CT[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]MA[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]ME[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]NH[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]RI[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]VT[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]NJ[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]NY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]DC[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]DE[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]MD[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]PA[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]VA[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]NWD[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]KS[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]MO[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]NE[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CO[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]MT[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]SD[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]WY[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]ID[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]OR[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]WA[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]POD[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]HI[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]AK[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Guam[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]SAD[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]AL[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]FL[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]GA[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]NC[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]SC[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]PR[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]VI[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]SPD[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]NM[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]UT[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]AZ[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]CA[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]NV[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]SWD[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]AR[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]OK[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]TX[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Excellent, that works. Thank you so much.

You are welcome.

 

[JPeterson]

Control+shift+enter, not just enter:

=IF(COUNTIF($B$3:$N$10,$A12),MIN(IF($B$3:$N$10=$A12,ROW($A$3:$A$10)-ROW($A$3)+1)),NA())

will return #N/A if A12 is not available in the value range.

I can't seem to get this portion to work, I would like the formula to return a blank cell if the lookup is not found. Do I need a combination of this formula along with the index listed above for this to work?

 

[Aladin Akyurek]

I can't seem to get this portion to work, I would like the formula to return a blank cell if the lookup is not found. Do I need a combination of this formula along with the index listed above for this to work?

You need to confirm the formula with control+shift+enter, not just enter:

=IF(COUNTIF($B$3:$N$10,$A12),INDEX($A$3:$A$10,MIN(IF($B$3:$N$10=$A12,ROW($A$3:$A$10)-ROW($A$3)+1))),"")

This returns the row field instead of a row number and a blank upon failure.

 

[JPeterson]

You need to confirm the formula with control+shift+enter, not just enter:

=IF(COUNTIF($B$3:$N$10,$A12),INDEX($A$3:$A$10,MIN(IF($B$3:$N$10=$A12,ROW($A$3:$A$10)-ROW($A$3)+1))),"")

This returns the row field instead of a row number and a blank upon failure.

It works! Great! Thank you much, I have been working on a solution for two weeks.

 

[Aladin Akyurek]

It works! Great! Thank you much, I have been working on a solution for two weeks.

Good news. You are welcome.