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

 

[Tennisguuy]

I manually typed over all of the counties. However the formula is not working for all states. It works for all of Alabama but after that I get 0 for the deductible.

 

[Aladin Akyurek]

I manually typed over all of the counties. However the formula is not working for all states. It works for all of Alabama but after that I get 0 for the deductible.

Ken,

Your data seems to have problems...

1) I'm not happy with the manual solution. The web sites has a character that koks like a space. In Excel it is a character with the code 160. if I were you I.d run the TrimAll macro on your data. You can find this macro on this board or via Google.

2) The after Alabama problem. Does this also occur with the control+shift+enter:

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

If it does, that could still mean the data is no clean. Try again after running the TrimAll macro.

 

[Tennisguuy]

I never heard of trim all macro. How do you do that. If I manually typed over the data would the erase anything that was previously copied or entered in the cell

 

[Aladin Akyurek]

I never heard of trim all macro. How do you do that. If I manually typed over the data would the erase anything that was previously copied or entered in the cell

See: Rearranging Data in Columns

 

[Tennisguuy]

Aladin thanks for all your help. I click on the link and unfortunately I'm not understanding how to perform the TRIM data work. Again thanks but I think I am just going to stop trying to figure out how to get this to work. Ken.

 

[Tennisguuy]

Oh forgot to mention I tried a couple of the sumifs and sumproduct formulas listed in the thread and they both worked about 90% of the time correctly. However as I was testing all a couple of times the deductible wouldn't change when I entered a different state or county.

These are the ones I used:

SUMIFS('Wind Deductible Chart'!D3:D1822,'Wind Deductible Chart'!A3:A1822,Sheet1!C7,'Wind Deductible Chart'!C3:C1822,Sheet1!D7)

SUMPRODUCT(('Wind Deductible Chart'!A3:A1822=C7)*('Wind Deductible Chart'!C3:C1822=Sheet1!D7)*('Wind Deductible Chart'!D3:D1822))

 

[Aladin Akyurek]

Aladin thanks for all your help. I click on the link and unfortunately I'm not understanding how to perform the TRIM data work. Again thanks but I think I am just going to stop trying to figure out how to get this to work. Ken.

The TrimAll code "should be copied to the standard code module which can be accessed by pressing Alt + F11. To run code from the Excel window, press Alt + F8, double click macro name".

Another option is to use ASAP Utilities.

 

[Tennisguuy]

Thanks that didn't work either . Going to give this one more last try. I am going to rearrange my data setup so that the normal index & match works.

 

[Tennisguuy]

Is there a way to determine if a value is enter twice in a column

 

[Tennisguuy]

Forgot and if so what that value is