Lookup for 4 criteria and retrieve the nearest match

hey guys,

I have 2 lists of items, consisting of 4 values (in column b,c,d and e). What I want to do is to find matches between sheets. Usually I use concatenate to group the all criteria i need to check, then i proceed. But there are no matches for most of the cases. for these ones, i just want to find the nearest matching values for column c,d and e from list 2 sheet into corresponding item in list 1 sheet.

I have found examples with 2 criteria, but i am not able to modify these INDEX and MATCH formulas for more criteria.

* first it should find an exact match for B column value from "list 2" sheet.

* if there is a match for data in B column from "list 2" sheet with specific corresponding cell in "list 1", formula should check for an exact match of C column. If there are no matches in C column between the corresponding cells, it should pick the nearest bigger match from "list 2" sheet.

* The process should continue for D and E columns with preference of exact matches, but ok to be picked the nearest bigger values.

* The most important point is all the picked items' concatenated version should already be existing in A column in second sheet. It should retrieve the item from A column from sheet 2 to sheet 1 with maximum similarity, and if available it should return with exact match.

to make it more clear, as long as it is available in A column from second sheet, it should pick an exact match for B, then C has the priority over D and E. It should select the item row in that order "Exact (B) > Nearest (C) > Nearest (D) = Nearest (E)" and retrieve the corresponding matching data from A column of that row from secondary sheet to first sheet.

Can anybody help me about this issue? any idea will be appreciated. file is attached for reference.

Thanks in advance.

* this question is also asked on reddit with title of: "lookup_for_4_criteria_and_retrieve_the_nearest". I am not able to post URLs as i am new on this forum.

Edited by GK. Here is the link:

https://www.reddit.com/r/excel/comme...e_the_nearest/

With more than 200 rows (a sample need not exceed 20 rows to facilitate checking), I have not checked this. However, I have added a column to show whether the match was exact or Approx>=.

A few rows of expected answers, manually calculated, would also have helped (as well as a smaller sample!!), as I am guessing what you want to see. But check this over.

=IFERROR(INDEX('list 2'!$A$2:$A$252,MATCH(1,INDEX(('list 2'!$B$2:$B$252='list 1'!B2)*('list 2'!$C$2:$C$252>='list 1'!C2)*('list 2'!$D$2:$D$252>=D2)*('list 2'!$E$2:$E$252>=E2),0),0)),"")

In Turkey, you may need to use ; instead of ,

I'll take another look. However, I forgot to tell you that I had to SORT the source data as well to get it working. So nearest matching... above OR below??

yes, to get it work, nearest matching. I would prefer bigger values and mentioned like that but i was not aware that i was leading to a dead-end, so any matches below or above is fine with me.
and thank you again. =)

Welcome (back) to the forum.

Thanks for your question. However, we have just found out that you have posted the same question elsewhere.... and haven't told us. Feel free to cross-post on other sites...

But. Every forum has its rules. Please see Forum Rule #3 about cross-posting. So.

Do not keep it a secret. We all are willing to give you our time, freely. However, it can be very annoying if we waste our time developing a solution for you, when you already have a nice solution elsewhere. All we ask is that you show us some respect and tell us:

1) if you have cross-posted, and

2) the URL of the cross posts.

That way, those of us who don't want to waste their time can quickly check to see if you're already happy with another solution.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

Thanks for providing the link.

We are not quite there yet, as I don't think you have defined your priority clearly enough. I suspect that this is likely to prove too complicated for my tiny brain. but, we will see.

I have created 5 Dynamic Named ranges (on top of the thousands of others that you have there already!!).

They are: All, Type, FR, Height & Width. They come in two sorts, depending on whether the values are NUMBERS or text. The Text ones look like this:

='list 2'!$A$2:INDEX('list 2'!$A:$A,MATCH("Zzzz",'list 2'!$A:$A))

and the number ones like this:

='list 2'!$C$2:INDEX('list 2'!$C:$C,MATCH(1E+100,'list 2'!$C:$C))

They will automatically adjust the ranges passed on to the formula to align with the data Table in List2.

In List1, F2, I used this to return EXACT matches:
=IFERROR(INDEX(All,MATCH(1,INDEX((Type=$B2)*(FR=$C2)*(Width=$D2)*(Height=$E2),0),0)),"")

In List 1, G2, I used this to return NEAREST match to FR, irrespective of the values of Height & Width. I didn't check it too closely (too many rows for much manual checking), but this MAY return silly matches that are wayyy out.

In List 1, H2, i did the same returning NEAREST match to Width, irrespective of the values of FR & Height.

Finally in List 1, I2, I did the same thing again, returning NEAREST match to Height, irrespective of the values of FR & Width.

So in column F, you have the best match possible. In G, H and I you have up to 3 possible answers.

HOWEVER. I strongly suspect that what you REALLY want is something that minimises the differences for ALL 3 parameters combined. If so, you will need to describe with 120% clarity how that needs to be done (mathematically, not in Excel terms).

Glenn,

It's obviously slayerweb's choice, but the mathematical option I raised in Post#2 of choosing the nearest "cubed" match (L x H x W) minimises the differences in all three paramaters combined.

Ochimus

Thanks, Ochimus... I missed that comment. Easy enough to implement... well within our "pay grade", as they say. We'll see what happens if/when he comes back.

Ochimus, I had also thought of a simple sum of the absolute deviations (L+W+H) or a "weighting" the three variable parameters (e.g. x 1 for length, x 0.5 for width and x 0.25 for height)... varying the weight in accordance with the OP's apparent L>W>H priority expressed somewhere (Here? Reddit?) a bit earlier; and then summing them.

Who knows...

I can explain the formula, once we agree the formula. See the attached sheet. Hopefully, if you answer the Qs correctly, I can reach a fimal result.

1. Does it matter if the measurements are GREATER than the specification or LESS than the specification? If it does not matter and your answer will be NO, it does matter, answer YES and tell me which is more important. More tolerance for values GREATER or more tolerance for values LESS. I think that I understand that the direction of the variance is not important.


2. Is one parameter more important than another? If which one(s) and by how much. In the sample file, I have made L the most important, followed by W (5 times less important than L) follwed by H (25 times less important than L). If there is a differenc ein priority, fill in the values in F4 to H4. If they are all equally important, leave these cells blank. These cells do not "do" anything yet. They are for information only.

3. I have populated a table with the LWH values for MD-04. (B8 to D46). In cells G8 to I8 and dnown, enter some real life choices that you have to make. Then look down the LWH values and manually choose the best fit in your judgement and enter the option number in J8. Continue until you get bored!! I will then review this to try to see if i can detect a pattern to your choices.

If C>D>E is the priotity order, try to assign them with their own weighting by: C*100+D*10+E for both range ,then subtract:
ABS((('list 2'!$C$2:$C$252*100+'list 2'!$D$2:$D$252*10+'list 2'!$E$2:$E$252)-($C2*100+$D2*10+$E2))
then get the smallest

and the final formula:

Exact match, F2:

Please Login or Register  to view this content.

nearest match , G2:

Please Login or Register  to view this content.