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]

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.

0. Make a copy of your workbook and activate the copy.

1. Run Alt+F11

2. Run Insert | Module

3. Copy the following the macro code:

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 2005-09-29 join.htm
'-- Rearranging Data in Columns
' - Optionally reenable improperly terminated Change Event macros
Application.DisplayAlerts = True
Application.EnableEvents = True 'should be part of Change Event macro
If Application.Calculation = xlCalculationManual Then
MsgBox "Calculation was OFF will be turned ON upon completion"
End If
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

and paste it in the right hand empty pane.

4. Run File | Close and Return to Microsoft Excel

5. Select the input data.

6. Run Developer | Macros | TrimAll

Now observe whether the Imdex\Match formulas succeed.

 

[Tennisguuy]

I did all of the above and it's still not working. Currently I have the data arranged vertically which is why I thought the double lookup would work. However I think I am going to rearrange the data on a x-y axis chart so the index & match formula work better. It will take a little while to do that. I will let you know how it works out. It might take about a week to because I will be out of the office traveling for next couple of days.

 

[Aladin Akyurek]

I did all of the above and it's still not working. Currently I have the data arranged vertically which is why I thought the double lookup would work. However I think I am going to rearrange the data on a x-y axis chart so the index & match formula work better. It will take a little while to do that. I will let you know how it works out. It might take about a week to because I will be out of the office traveling for next couple of days.

If the data is not ok, nothing will work. Try to post a small sample from the original data along with the actual expected results.

 

[Tennisguuy]

The data is ok. After I rearranged the data into a x/y axis and did a standard index and match everything works. Some of the errors were also do to duplicates. Once I got rid of all of the duplicates and there were a lot and rearranged the data everything works now.

Thanks for all of your help.

 

[Aladin Akyurek]

The data is ok. After I rearranged the data into a x/y axis and did a standard index and match everything works. Some of the errors were also do to duplicates. Once I got rid of all of the duplicates and there were a lot and rearranged the data everything works now.

Thanks for all of your help.

Thiat's great... It's unclear though why a multiconditional index\match would not succeed. And thanks for the feedback.

 

[Tennisguuy]

No problem. Thanks for all of your assistance and sticking with me through it. I really appreciate it.