Index Match with multiple nested if statements

E

equals

New Member
Joined
Sep 22, 2017
Messages
2
Hi,
I have two tables, one with "source" data as follows:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[/TR]
[TR]
[TD]Lookup[/TD]
[TD]Helper[/TD]
[TD]High[/TD]
[TD]Low[/TD]
[TD]Geo_Lookup[/TD]
[TD]Geo_High[/TD]
[TD]Geo_Low[/TD]
[TD]Other[/TD]
[TD]Class[/TD]
[TD]Description[/TD]
[TD]Important[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD]Apples & Oranges[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]#ALT [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Apples & Pears[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]A2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]55[/TD]
[TD]High Apples & Pears[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]56[/TD]
[TD]Low Apples & Pears[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]A2a[/TD]
[TD]A2a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13[/TD]
[TD]Oranges & Plums[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]A2c[/TD]
[TD]#ALT 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]14[/TD]
[TD]Plums & Pears[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]A2c[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[TD]Pears & Plums High[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A2c[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pears & Plums Low[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]#GEO [/TD]
[TD][/TD]
[TD][/TD]
[TD]#GEO _ALT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Apples & Pears[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]B1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Apples & Pears High[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Apples & Pears Low[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]GEOApples & Pears High[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B1[/TD]
[TD][/TD]
[TD][/TD]
[TD]GEOApples & Pears Low[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B1[/TD]
[TD][/TD]
[TD]OTHER Apples & Pears[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]B2[/TD]
[TD]B2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]Oranges & Pears[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]B2a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD]Kiwi & Lime[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]B3[/TD]
[TD]#GEO [/TD]
[TD][/TD]
[TD][/TD]
[TD]#GEO _ALT[/TD]
[TD][/TD]
[TD][/TD]
[TD]#ALT [/TD]
[TD]11[/TD]
[TD]Lime[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]B3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lime High[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lime Low[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]GEO Lime High[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Geo Lime Low[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]

And a second "results" table which I wish to write some formulae for to lookup the description based upon its Geology and Height

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD]Uid[/TD]
[TD]Definition[/TD]
[TD]Resolves_to[/TD]
[TD]Description[/TD]
[TD]Important[/TD]
[TD]Geo[/TD]
[TD]Height[/TD]
[TD]Class[/TD]
[/TR]
[TR]
[TD]G1[/TD]
[TD]A1a[/TD]
[TD]A1[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G2[/TD]
[TD]B3[/TD]
[TD]#GEO [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G3[/TD]
[TD]A2a[/TD]
[TD]A2a[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G4[/TD]
[TD]A2[/TD]
[TD]#ALT [/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G5[/TD]
[TD]B2b[/TD]
[TD]B2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G6[/TD]
[TD]B2[/TD]
[TD]B2[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]36[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The definition code is not always contained in the lookup table, but its parent code is so I am using the following formula to check if its present and remove the trailing sub category (the trailing alpha character) if it is not:
{=IFNA(VLOOKUP(B2:B13268,Source_Lookup_Table!A:M,2,FALSE),VLOOKUP(IF(ISNUMBER(-RIGHT(B2:B13268)),B2:B13268,LEFT(B2:B13268,LEN(B2:B13268)-1)),Source_Lookup_Table!A:M,2,FALSE))}

I would like to populate the "results" table columns D, E and H with the description/importance/class of the closest category.

The helper # codes need the following logic:
#ALT
Where "Source_Lookup_Table!B:B" = "#ALT " then choose between columns C (High) and D (Low) based upon Height >=50

#ALT 2
Where "Source_Lookup_Table!B:B" = "#ALT 2" then choose between columns C (High) and D (Low) based upon Height <=10

#GEO
Where "Source_Lookup_Table!B:B = "#GEO " check if "Results_Table!F:F" (Geo column) = A
If it does - Check the altitude is >=50 and lookup column F (Geo_High) or if <50 column G (Geo_Low)
If Column F (Geo) = B then check if it exists (or its non sub category) as a separate row in column H - if NA (cannot be found) then find it in Column A and then read column H which will resolve to "#ALT " and can therefore be resolved using columns C and D as above.

This is where I am at so far (although I currently have a few more columns in my sheet) - I am using index match so I can write the formulae and then just change the column number match resolves to extract the data I need:
{=IFS((M2:M13268>=50)*(G2:G13268="#ALT "),(IFNA(INDEX(Source_Lookup_Table!C:C,MATCH(E2:E13268,Source_Lookup_Table!C:C,0)),INDEX(Source_Lookup_Table!C:C,MATCH(F2:F13268,Source_Lookup_Table!C:C,0)))),(M2:M13268<50)*(G2:G13268="#ALT "),(IFNA(INDEX(Source_Lookup_Table!D:D,MATCH(E2:E13268,Source_Lookup_Table!D:D,0)),INDEX(Source_Lookup_Table!D:D,MATCH(F2:F13268,Source_Lookup_Table!D:D,0)))),(M2:M13268<=10)*(G2:G13268="#ALT 2"),IFNA(INDEX(Source_Lookup_Table!D:D,MATCH(E2:E13268,Source_Lookup_Table!D:D,0)),INDEX(Source_Lookup_Table!D:D,MATCH(F2:F13268,Source_Lookup_Table!D:D,0))),(M2:M13268>10)*(G2:G13268="#ALT 2"),IFNA(INDEX(Source_Lookup_Table!C:C,MATCH(E2:E13268,Source_Lookup_Table!C:C,0)),INDEX(Source_Lookup_Table!C:C,MATCH(F2:F13268,Source_Lookup_Table!C:C,0))),G2:G13268="#GEO ",VLOOKUP(F2:F13268,Source_Lookup_Table!A:M,5,FALSE),TRUE,G2:G13268)}

Because I have >13200 rows I am using {array formula}. but I have discovered lots of things like AND do not work so I have tried IF((A1>3)*(B1="#GEO ")) and lots of ISNUMBER statements to keep things true of false. I have managed to get somewhere with the first two bits of logic, but it has failed again when trying to implement the ""#GEO " logic.. It is also taking a very long time to calculate on my laptop, causing excel to freeze for minutes at a time so I was hoping there might be a more efficient method?

Any guidance very gratefully received.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
I meant to say, column E (geo_lookup) can have a value (i.e. A3b) or "#GEO_ALT" which is when I would like to check the altitude and resolve between Column F (Geo_High) and Column G (Geo_Low)
 
Upvote 0
You must log in or register to reply here.

Similar threads

megera716
  • Question Question
INDEX/MATCH for lookup criteria and value between low/high range (like VLOOKUP "TRUE")
Replies
3
Views
386
etaf
etaf
J
  • Solved
Help Index Match
Replies
3
Views
495
KRice
K
C
  • Question Question
Stock inventory - count unique sequences from barcode
Replies
0
Views
574
ChemistryBMS
C
M
  • Question Question
return multiple matching values into one cell based on multiple criteria in Excel
Replies
3
Views
448
MMM_84
M
B
  • Solved
Using INDEX MATCH & LARGE to get value in multiple columns
Replies
3
Views
490
bunny1122
B

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

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