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
JoeCavasin's avatar
JoeCavasin
Brass Contributor
Jan 12, 2019

Thanks Sergei!  I was wondering if that would be the issue, but didn't know the appropriate terminology.  

 

I need to match on two criteria - from my screenshot here - I need to pull back(INDEX) column "C" based on a match of columns E, and A.  Column A is a unique product.  Column E having a number present (it will always be 1541) represents that the column A product number is available through the Column E seller.  is there no way to work around that in excel so that a Index(, Match(), Match()) function could supply the answers?  Or does the repeating data point in column E automatically make this unsolvable?

 

 

I understand this may seem a bit confusing without a full set of data.  if there might be a workaround, i can dummy up some non-company specific stuff to attach.

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