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]

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

<tbody>
</tbody>

A12: OH

B12, control+shift+enter, not just enter:

=INDEX($A$2:$A$9,MIN(IF($B$2:$N$9=$A12,ROW($A$2:$A$9)-ROW($A$2)+1)))

If some control is desired...

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

 

[Tomitsch]

I tried both options. The first one I get either a #Ref or incorrect answer as shown below. It was entered as an array. The second I didn't get to work at all. I rearranged everything so that I didn't have problems with the reference you used. The table below is aligned to A1.

Thanks in advance.

This is Cell A1

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

State Code

Region

TX

#REF!

<<{=INDEX($A$3:$A$10,MIN(IF($B$3:$N$10=$A12,ROW($A$3:$A$10)-ROW($A$2)+1)))}

NY

NWD

NJ

NWD

VA

NWD

IN

MVD

AL

SPD

FL

SPD

MI

MVD

<colgroup><col style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;" width="92"> <col style="width: 48pt;" width="64"> <col style="width: 48pt;" span="12" width="64"> <tbody>
</tbody>

 

[Aladin Akyurek]

I tried both options. The first one I get either a #Ref or incorrect answer as shown below. It was entered as an array. The second I didn't get to work at all. I rearranged everything so that I didn't have problems with the reference you used. The table below is aligned to A1.

Thanks in advance.

This is Cell A1

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

State Code

Region

TX

#REF!

<<{=INDEX($A$3:$A$10,MIN(IF($B$3:$N$10=$A12,ROW($A$3:$A$10)-ROW($A$2)+1)))}

NY

NWD

NJ

NWD

VA

NWD

IN

MVD

AL

SPD

FL

SPD

MI

MVD

<tbody>
</tbody>

It should be:

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

in accordance with $A$3:$A$10. Recall that you need to confirm the formula with control+shift+enter.

 

[Tomitsch]

Thanks so much. It's a thing of beauty

 

[Aladin Akyurek]

Thanks so much. It's a thing of beauty

You are welcome. Thanks for the kind feedback.

 

[Aladin Akyurek]

Thanks so much. It's a thing of beauty

You are welcome. Thanks for the kind feedback.

 

[Sherrell]

Hello, this is very helpful and exactly what I'm looking to do, but I have two questions. When the value entered in A12 does not exist in b2:a9, why is the returned value LRD? And is there a way to return an error when there is no match? For example, A12=ZZ, B12=LRD, but this is misleading as ZZ is not in row 2.
Thanks!

 

[Aladin Akyurek]

Hello, this is very helpful and exactly what I'm looking to do, but I have two questions. When the value entered in A12 does not exist in b2:a9, why is the returned value LRD? And is there a way to return an error when there is no match? For example, A12=ZZ, B12=LRD, but this is misleading as ZZ is not in row 2.
Thanks!

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.

 

[Sherrell]

Excellent, that works. Thank you so much.