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

Lookup Table

Region

States

LRD

WV

KY

TN

IN

OH

MI

MVD

MS

IL

MN

WI

LA

IA

ND

NAD

CT

MA

ME

NH

RI

VT

NJ

NY

DC

DE

MD

PA

VA

NWD

KS

MO

NE

CO

MT

SD

WY

ID

OR

WA

POD

HI

AK

Guam

SAD

AL

FL

GA

NC

SC

PR

VI

SPD

NM

UT

AZ

CA

NV

SWD

AR

OK

TX

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

 

[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.