Multi Level Index Match IF - open to suggestions

[JoeCavasin]

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 data set) bogged down forcing this review.  Also,  this data set comes out in the manner of the screenshot below, and for compliance concerns, the intent is to not modify the data set from the script - just allow formulas to do the heavy lifting and remove human error from the process.

The issue:

"Template" is where i assemble data via multiple formulas.  One of these forumlas in Template column C will compare Template column A to "Standard" sheet columns A through H.  If it finds a match in "Standard" columns A through H, it needs to return a value out of Standard Column U.    Attached here is a screenshot of a dummy version of the "STANDARD" sheet for reference.

I had previously tried to nest an "IF" within the index/match, but i believe the problem is that Match must have a single dimensional range to evaluate.  I don't know how to work around that.  i should also mention I know vlookup won't work reliably as this sheet contains upwards of 3000 lines and has bogged down in the past in a simpler data set of the same info, causing the need for this template and lookup re-evaluation.

Thanks in advance - open to all suggestions except macros as those are well outside of my abilities, and this will be utilized by multiple folks besides me who have even less excel abilities...

-Joe

[#2]

Hello! As you mentioned: it’s complex. Either it’s complex for me to figure out your different questions. Would be easier to help if you can articulate one question after another on your own. Greets, Eva

[#3]

Hi Joe,

You may use simulation of INDEX/MATCH on 2D array. For data like here

the formula could be

= IF(SUMPRODUCT(--($B$4:$H$12=K1))=0, "No match",INDEX($U$4:$U$12,SUMPRODUCT(($B$4:$H$12=K1)*ROW($B$4:$H$12))-ROW($B$4:$H$12)+1))

However, it works if the lookuped data occurs in your array not more than once.

[#4]

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.

[#5]

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

[#6]

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

[#7]

Hi Joe,

The pattern is explained here https://exceljet.net/formula/get-location-of-value-in-2d-array