Double Vertical Lookup

[Tennisguuy]

I can do a regular vertical lookup but not quite sure how to do a double vertical lookup. I need to look up the state and then the county within my table to find the correct deductible. The lookup value for the state is in cell A12 and the lookup value for the county is in cell A13. The data is in cell K12:N500. The state is in column K, location is in column L, county column M and deductible column N

 

[Aladin Akyurek]

For example for the state of Alabama I have the counties of Baldwin, barbour

County
baldwin
*Barbour
*Bibb
Blout
*Bullock
*Butler
*Calhoun
*Chambers
*Cherokee
*Chilton
*Choctaw

The problem isn't with what I have listed. If I type over the counties that I copied from the internet , the formula will work meaning it will show the correct deductible. Excel is just not recognizing what I copied and pasted into excel from the internet. Not sure if it because I copied them from a website page to use in the formula. I tried format column as text and still nothing but soon as I manually type over them it works.

If you want I can send it to you but not sure how to do that on the site.

Try this with a small tweak added...

=IFERROR(INDEX($N$2:$N$100,MATCH(A13,INDEX(IF($K$2:$K$100=A12,"~"&$M$2:$M$10),0,1),0)),0)

 

[Tennisguuy]

I tried your suggestion, now everything returns a zero even for a county where I manually typed over the county.

 

[Aladin Akyurek]

I tried your suggestion, now everything returns a zero even for a county where I manually typed over the county.

What would be the county criterion - *Barbour or Barbour?

What is the length of the original entry of *Barbour?

=LEN([The cell which houses *Barbour])

 

[Tennisguuy]

There is not a criteria for any of the counties. The length of each county will vary based on the name of the county. In the example I provided you can see each name will vary in length. The counties names are the county names for any state listed in the United States which is why I thought I could try to get a list from the internet instead of manually entering them in myself.

 

[Aladin Akyurek]

There is not a criteria for any of the counties. The length of each county will vary based on the name of the county. In the example I provided you can see each name will vary in length. The counties names are the county names for any state listed in the United States which is why I thought I could try to get a list from the internet instead of manually entering them in myself.

That is not what I m asking...

What is the length of *Barbour entry that you pulled of internet?

Would you give some example values for A12 and A13, the look up values the formula

=IFERROR(INDEX($N$2:$N$100,MATCH(A13,INDEX(IF($K$2:$K$100=A12,$M$2:$M$10),0,1),0)),0)

is using?

 

[Tennisguuy]

Sorry the length that I pulled from the internet for Barbour was 13 characters long. All of the counties I pulled from the internet included the word county in them. I used the following formula to extract =left(C1,len(c1)-8).

I am using your formula above with some minor changes because I moved the data to another sheet. I am using the following formula now.

IFERROR(INDEX('Wind Ded Chart'!$D$3:$D$1823,MATCH(D7,INDEX(IF('Wind Ded Chart'!$A$3:$A$1823=C7,'Wind Ded Chart'!$C$3:$C$1823),0,1),0)),0)

I left the lookup values on the same sheet but move them to cell C7 and D7.

Some examples in cell C7 are

AL
AR
MS
MO
FL

Some of the values in cell D7 are

Baldwin
Bullock
Cross
Bradley
Franklin
Gulf

 

[Aladin Akyurek]

Is 'Wind Ded Chart'!$C$3:$C$1823 produced with =left(C1,len(c1)-8) ?

 

[Tennisguuy]

No I used that formula to exact the word county from the counties I downloaded from the internet. Once I extracted the word county, I pasted those values into this spread. I used another spreadsheet to extract the data from the internet. Once I got it in the all together I copied it from that spreadsheet into this spreadsheet.

 

[Aladin Akyurek]

No I used that formula to exact the word county from the counties I downloaded from the internet. Once I extracted the word county, I pasted those values into this spread. I used another spreadsheet to extract the data from the internet. Once I got it in the all together I copied it from that spreadsheet into this spreadsheet.

Ken,

I think you got char(160) around the entries you gatheref from internet.

My question was: Did you produce the lookup area with counties with that formula or not? If you didn't, I don't understand why I get the formula which I can't relate to anything (not to C7 or D7 anyway)...

 

[Tennisguuy]

I'm not sure what your are exactly asking but I will just let you know what I have set up. On sheet 1 I have the following set up

State Abb(Cell C6) County(D6) Deductible(E6) This is just the heading.

In cell c7 I enter the state and in cell D7 I enter the county. In cell E7 I have the following formula to lookup the deductible.

IFERROR(INDEX('Wind Ded Chart'!$D$3:$D$1823,MATCH(D7,INDEX(IF('Wind Ded Chart'!$A$3:$A$1823=C7,'Wind Ded Chart'!$C$3:$C$1823),0,1),0)),0)

Then I have another sheet labeled Wind Ded Chart with the headings on row 2. Cell A2 is the state, B2 is the Region, C2 is the county and D2 is the deductible

In cells A3:D183 I have the data. In column A I have the states, in column B I have the region, column C I have the counties and column D I have the deductible

Sorry if I have not following what exactly you're asking.