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

 

[DKaur]

A lookup function would ignore any duplicate rows below the first it has found. I would have thought you'd want to include these ones. If not, then I don't see why the topmost entry should be the one included, more likely to have the bottom-most row since this would likely be the latest entry.

But I completely agree with the data type issue. However, we are dealing with a number.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

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

You have to use ctrl+shift+enter for array formulas. If you don't it won't work properly.

Do not get seduced by functions that sum instead of retrieve.

You should not fear control+shift+enter either for a sizeable class of problems requires array-processing formulas.

Since you are asking for:

Just enter:

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

Recall that this still an array-processing formula.

 

[Tennisguuy]

thanks that did work. However not sure how to correct my other problem. For the counties I copied them from the internet. They counties showed on the internet as an hyper link but when I copied them to excel I selected them and right click and paste special. However we I use the formula the return value for the deductible is zero. Therefore I retyped over the first county and enter the county manually and the formula work. Is there anyway I can change the other counties without physically typing over them because there are almost 2,000 counties I copied from the internet.

 

[Weazel]

try selecting the column....right click....remove hyperlink ? Maybe

 

[Tennisguuy]

That's not an option when I right click. I didn't use the hyperlink value but instead copied them and selected paste value. I thought that would work because it didn't show them as a hyperlink

 

[Tennisguuy]

Aladin I tried your revision and that worked too but still having a problem with the counties that I copied from the internet. If I retype them the formulas work but otherwise the formula just returns a 0.

 

[Aladin Akyurek]

Aladin I tried your revision and that worked too but still having a problem with the counties that I copied from the internet. If I retype them the formulas work but otherwise the formula just returns a 0.

Which range does this involve?

 

[Tennisguuy]

It involves the county column. Since there were so many counties over 1,800 I searched the internet to see if I could find a site that listed the counties and I did but they list them with a hyperlink. I also found another website that didn't list them as a hyper link and when I copied them from that website too and pasted them in the spreadsheet the formula doesn't see to recognize the value. I don't get a value error or anything it just returns a zero for the deductible. However if I manually type over that county the formula returns the correct deductible value. It does it for all the counties in the list.

 

[Aladin Akyurek]

It involves the county column. Since there were so many counties over 1,800 I searched the internet to see if I could find a site that listed the counties and I did but they list them with a hyperlink. I also found another website that didn't list them as a hyper link and when I copied them from that website too and pasted them in the spreadsheet the formula doesn't see to recognize the value. I don't get a value error or anything it just returns a zero for the deductible. However if I manually type over that county the formula returns the correct deductible value. It does it for all the counties in the list.

Would you post same examples from the county column?

 

[Tennisguuy]

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.