Lookup across multiple columns and row to produce a single result

T

Tomitsch

New Member
Joined
Jun 27, 2013
Messages
3
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 :rofl:

:rofl:[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]
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Tomitsch said:
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 :rofl:

:rofl:[TABLE="width: 672"]
<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]
Click to expand...

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))),"")
 
Upvote 0
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.

[TABLE="width: 693"]
<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>[TR]
[TD="class: xl77, width: 92, bgcolor: #FFE699"]This is Cell A1[/TD]
[TD="class: xl75, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"] [/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"] [/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: #548235"]Region[/TD]
[TD="class: xl73, bgcolor: #305496"]States[/TD]
[TD="class: xl73, bgcolor: #305496"] [/TD]
[TD="class: xl73, bgcolor: #305496"] [/TD]
[TD="class: xl73, bgcolor: #305496"] [/TD]
[TD="class: xl73, bgcolor: #305496"] [/TD]
[TD="class: xl73, bgcolor: #305496"] [/TD]
[TD="class: xl73, bgcolor: #305496"] [/TD]
[TD="class: xl73, bgcolor: #305496"] [/TD]
[TD="class: xl73, bgcolor: #305496"] [/TD]
[TD="class: xl73, bgcolor: #305496"] [/TD]
[TD="class: xl73, bgcolor: #305496"] [/TD]
[TD="class: xl73, bgcolor: #305496"] [/TD]
[TD="class: xl74, bgcolor: #305496"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 92, bgcolor: white"]LRD[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]WV[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]KY[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]TN[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]IN[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]OH[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]MI[/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 92, bgcolor: white"]MVD[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]MS[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]IL[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]MN[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]WI[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]LA[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]IA[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]ND[/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 92, bgcolor: white"]NAD[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]CT[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]MA[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]ME[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]NH[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]RI[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]VT[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]NJ[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]NY[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]DC[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]DE[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]MD[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]PA[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]VA[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 92, bgcolor: white"]NWD[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]KS[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]MO[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]NE[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]CO[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]MT[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]SD[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]WY[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]ID[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]OR[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]WA[/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 92, bgcolor: white"]POD[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]HI[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]AK[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Guam[/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 92, bgcolor: white"]SAD[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]AL[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]FL[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]GA[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]NC[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]SC[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]PR[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]VI[/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 92, bgcolor: white"]SPD[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]NM[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]UT[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]AZ[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]CA[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]NV[/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 92, bgcolor: white"]SWD[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]AR[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]OK[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]TX[/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]State Code[/TD]
[TD="class: xl70, bgcolor: #FFE699"]Region[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]TX[/TD]
[TD="class: xl72, bgcolor: #FFE699, align: center"]#REF![/TD]
[TD="class: xl78, bgcolor: white"]<<{=INDEX($A$3:$A$10,MIN(IF($B$3:$N$10=$A12,ROW($A$3:$A$10)-ROW($A$2)+1)))}[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]NY[/TD]
[TD="class: xl72, bgcolor: #FFE699"]NWD[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]NJ[/TD]
[TD="class: xl72, bgcolor: #FFE699"]NWD[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]VA[/TD]
[TD="class: xl72, bgcolor: #FFE699"]NWD[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]IN[/TD]
[TD="class: xl72, bgcolor: #FFE699"]MVD[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]AL[/TD]
[TD="class: xl72, bgcolor: #FFE699"]SPD[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]FL[/TD]
[TD="class: xl72, bgcolor: #FFE699"]SPD[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]MI[/TD]
[TD="class: xl72, bgcolor: #FFE699"]MVD[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Tomitsch said:
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.

[TABLE="width: 693"]
<tbody>[TR]
[TD="class: xl77, width: 92, bgcolor: #FFE699"]This is Cell A1
[/TD]
[TD="class: xl75, width: 64, bgcolor: #FFE699"][/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"][/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"][/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"][/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"][/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"][/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"][/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"][/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"][/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"][/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"][/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"][/TD]
[TD="class: xl72, width: 64, bgcolor: #FFE699"][/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: #548235"]Region
[/TD]
[TD="class: xl73, bgcolor: #305496"]States
[/TD]
[TD="class: xl73, bgcolor: #305496"][/TD]
[TD="class: xl73, bgcolor: #305496"][/TD]
[TD="class: xl73, bgcolor: #305496"][/TD]
[TD="class: xl73, bgcolor: #305496"][/TD]
[TD="class: xl73, bgcolor: #305496"][/TD]
[TD="class: xl73, bgcolor: #305496"][/TD]
[TD="class: xl73, bgcolor: #305496"][/TD]
[TD="class: xl73, bgcolor: #305496"][/TD]
[TD="class: xl73, bgcolor: #305496"][/TD]
[TD="class: xl73, bgcolor: #305496"][/TD]
[TD="class: xl73, bgcolor: #305496"][/TD]
[TD="class: xl74, bgcolor: #305496"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 92, bgcolor: white"]LRD
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]WV
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]KY
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]TN
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]IN
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]OH
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]MI
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 92, bgcolor: white"]MVD
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]MS
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]IL
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]MN
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]WI
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]LA
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]IA
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]ND
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 92, bgcolor: white"]NAD
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]CT
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]MA
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]ME
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]NH
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]RI
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]VT
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]NJ
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]NY
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]DC
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]DE
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]MD
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]PA
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]VA
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 92, bgcolor: white"]NWD
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]KS
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]MO
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]NE
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]CO
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]MT
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]SD
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]WY
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]ID
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]OR
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]WA
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 92, bgcolor: white"]POD
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]HI
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]AK
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Guam
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 92, bgcolor: white"]SAD
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]AL
[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]FL
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]GA
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]NC
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]SC
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]PR
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]VI
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 92, bgcolor: white"]SPD
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]NM
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]UT
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]AZ
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]CA
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]NV
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 92, bgcolor: white"]SWD
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]AR
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]OK
[/TD]
[TD="class: xl67, width: 64, bgcolor: white"]TX
[/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]State Code
[/TD]
[TD="class: xl70, bgcolor: #FFE699"]Region
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]TX
[/TD]
[TD="class: xl72, bgcolor: #FFE699, align: center"]#REF!
[/TD]
[TD="class: xl78, bgcolor: white"]<<{=INDEX($A$3:$A$10,MIN(IF($B$3:$N$10=$A12,ROW($A$3:$A$10)-ROW($A$2)+1)))}
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]NY
[/TD]
[TD="class: xl72, bgcolor: #FFE699"]NWD
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]NJ
[/TD]
[TD="class: xl72, bgcolor: #FFE699"]NWD
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]VA
[/TD]
[TD="class: xl72, bgcolor: #FFE699"]NWD
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]IN
[/TD]
[TD="class: xl72, bgcolor: #FFE699"]MVD
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]AL
[/TD]
[TD="class: xl72, bgcolor: #FFE699"]SPD
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]FL
[/TD]
[TD="class: xl72, bgcolor: #FFE699"]SPD
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]MI
[/TD]
[TD="class: xl72, bgcolor: #FFE699"]MVD
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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.

 
Upvote 0
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!
 
Upvote 0
Sherrell said:
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!
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Populating a ListBox with Columns Using Pre-Filled Array
Replies
5
Views
547
Legacy 143009
L
C
  • Question Question
Match Top Results
Replies
3
Views
388
Fluff
Fluff
M
  • Question Question
Lookup multiple values in a single cell (separated by commas) and then return the values to a single cell
Replies
3
Views
715
63falcondude
63falcondude
I
  • Question Question
LOOKUP across multiple ranges (noncontinuous references)
2 3
Replies
24
Views
2K
itfmm
I
R
  • Question Question
Prompt User for Range Input and then Use in Formula
Replies
3
Views
1K
tonyyy
tonyyy

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

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
Back
Top