Find a specific value from a Multiple criteria range

[Satyam Koli]

Hello,

On the attached are 2 sheets
Details where value is required in col H and I
Master is the database from where value has to be pulled

Value should match the criteria
Shape from Col A in details with Shape in Col I in Master
Color from Col B in details with color in Col G in Master
Quality from Col C in details with Quality in Col H in master
Wt per from Col E in details should be between the min in Col D and max in Col E in master

first example in Details
PJC G VS2 and 0.67
since its between 0.59 - 0.69 the material code is #C#7ZZZZ2O2Y

If matching value is not found it should say "NOT FOUND" like the row 12 onwards on details sheet the shape is not on the master

Thank you,
Satyam Koli

 

[xlstime]

Hey Satyam,

Please refer attachment, hope this will help

Hello,

On the attached are 2 sheets
Details where value is required in col H and I
Master is the database from where value has to be pulled

Value should match the criteria
Shape from Col A in details with Shape in Col I in Master
Color from Col B in details with color in Col G in Master
Quality from Col C in details with Quality in Col H in master
Wt per from Col E in details should be between the min in Col D and max in Col E in master

first example in Details
PJC G VS2 and 0.67
since its between 0.59 - 0.69 the material code is #C#7ZZZZ2O2Y

If matching value is not found it should say "NOT FOUND" like the row 12 onwards on details sheet the shape is not on the master

Thank you,
Satyam Koli

 

[bosco_yip]

In Details Sheet H5, formula copy across and down :

=IF(ISNUMBER(MATCH($J5,'Material code master'!$J$14:$J$33,0)),LOOKUP($E5,'Material code master'!$D$14:$D$33*($J5='Material code master'!$J$14:$J$33),'Material code master'!A$14:A$33),"NOT FOUND")

Regards
Bosco

 

[Villalobos]

Hi,

Check out this array approach.

Note: I changed the decimal separators of MIN and MAX (on sheet Material code master) from point to comma and converted to numbers.

 

[Satyam Koli]

Thank you this was helpful

xlstime i didn't find any formula on the file you uploaded
Villalobos- how does substitute help here as it works even without using substitute

Thank you once again
Satyam Koli

 

[Villalobos]

Hi,

The standard decimail separator is comma on my PC therefore I had to modify the MIN and MAX columns to get numbers.

 

[xlstime]

Sorry attached wrong file, here you go

Thank you this was helpful

xlstime i didn't find any formula on the file you uploaded
Villalobos- how does substitute help here as it works even without using substitute

Thank you once again
Satyam Koli