Lookup value for similar cells

[AtulVaidya]

Hi,

I have certain cells concatenated for reference, to return the corresponding values in another cell, based on the nearest "strength" of the product.

Need help basis the attached Excel sheet uploaded for reference.

Thanks in advance.

[JeteMc]

Hello AtulVaidya and Welcome to Excel Forum.
This proposal employs three helper columns (L:N) which may be moved and/or hidden for aesthetic purposes.
1. Column L is populated using: =IF(ISERROR(VLOOKUP($G2,$A:$E,5,0)),AGGREGATE(14,6,C$2:C$31/(B$2:B$31=LEFT(G2,3))/(D$2:D$31=RIGHT(G2,2))/(C$2:C$31<MID(G2,4,SEARCH("z",G2)-4)+0),1),"")
2. Column M is populated using: =IF(IFERROR(L2<>"",TRUE),AGGREGATE(15,6,C$2:C$31/(B$2:B$31=LEFT(G2,3))/(D$2:D$31=RIGHT(G2,2))/(C$2:C$31>MID(G2,4,SEARCH("z",G2)-4)+0),1),"")
3. Column N is populated using: =IF(IFERROR(L2<>"",TRUE),IF(ISERROR(L2),M2,IF(ISERROR(M2),L2,IF(ABS(L2-MID(G2,4,SEARCH("z",G2)-4)+0)<ABS(M2-MID(G2,4,SEARCH("z",G2)-4)+0),L2,M2))),"")
4. Column H is populated using: =IFERROR(VLOOKUP($G2,$A:$E,5,0),VLOOKUP(LEFT(G2,3)&N2&RIGHT(G2,2),A:E,5,0))
Let us know if you have any questions.

[AtulVaidya]

Hello JeteMc,

Many thanks for your response and at the first glance through the 3 columns, it does seem like a workable solution.

Will get back to you, as soon as I try it out on my file!!!

Thanks once again

Regards,
Atul.

[AtulVaidya]

Hi JeteMc,

Firstly, thanks once again for taking time out to provide me with a solution on the above query.

However, there is a change in the specs provided by me in the Sample File - the name of the product could have as many as 10-12 alphabets too, and not necessarily just 3 - as shared by me as an example.

Apologies and many thanks in advance to help me resolve the same.

Regards,
Atul.

[JeteMc]

I believe that this will work, however if not then please upload another file with data that represents the 10-12 letters in the product name.
1. Add a column (O) populated using**: =MIN(IFERROR(SEARCH({"0","1","2","3","4","5","6","7","8","9"},G2),LEN(G2)))-1
2. Modify the formula in column L to read: =IF(ISERROR(VLOOKUP($G2,$A:$E,5,0)),AGGREGATE(14,6,C$2:C$31/(B$2:B$31=LEFT(G2,O2))/(D$2:D$31=RIGHT(G2,2))/(C$2:C$31<MID(G2,O2+1,SEARCH("z",G2)-(O2+1))+0),1),"")
3. Modify the formula in column M to read: =IF(IFERROR(L2<>"",TRUE),AGGREGATE(15,6,C$2:C$31/(B$2:B$31=LEFT(G2,O2))/(D$2:D$31=RIGHT(G2,2))/(C$2:C$31>MID(G2,O2+1,SEARCH("z",G2)-(O2+1))+0),1),"")
**Denotes an array formula which is not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.

[nick.williams]

If you wanted to avoid helper columns, I think this will work, if you can change your Names+Strength+Code to use a separator. In this example I used _ e.g. ABC_13000_ZB instead of ABC13000ZB.

Please Login or Register  to view this content.

If you don't have 365 then this is it without the LET:

Please Login or Register  to view this content.

[AtulVaidya]

Thank you so much for your reply and efforts to help me resolve the problem.

Due to some personal reasons I was unable to log in for quite some time and hence sincere apologies for the delayed revert from my side.

I shall try the above mentioned solution and see if it works.

Thanks & Warm Regards,
Atul.