Forum Discussion

JoeCavasin's avatar
JoeCavasin
Brass Contributor
Jan 12, 2019

Multi Level Index Match IF - open to suggestions

Morning all, need some assistance.  Have a very complex lookup matching process i need to improve, index match has some limitations, and vlookup (in a previously much more simplified version of the d...
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 12, 2019

I'm not sure I understood the logic of your sheets correctly since the same data is repeated in many places, in different columns and in different sheets.

 

Anyway, if to simplify Standard sheet to

and Template one to

and assuming no one combination is repeated in Standard (other words, numbers like 1541_454 are never repeated), the formula for column C in template could be

= IF(SUMPRODUCT(--(Standard!$A$2:$H$6=(E2&"_"&A2)))=0, "No match",INDEX(Standard!$U$2:$U$6,SUMPRODUCT((Standard!$A$2:$H$6=(E2&"_"&A2))*ROW(Standard!$A$2:$H$6))-ROW(Standard!$A$2:$H$6)+1))

If above assumption is not correct when we need something else

JoeCavasin's avatar
JoeCavasin
Brass Contributor
Jan 15, 2019

before i modify the info i supplied (it looks like we had a miscommunication on the shortened data set) - can you help me understand the SUMPRODUCT function?  that is not something i've used and even reading the excel help documentation on it leaves me confused as all get out.

 

Thanks again!

Joe

Resources