Lookup value on seperate sheet based upon cell value

What I am trying to do is ensure that data is valid by cross referencing it to data on another sheet. Ive uploaded my workbook. The formula would be in the "shipped parts" worksheet in the I column. What I want it to do is search for the value in the D column, looking in the worksheet "material" under the C colum value.

Lets take the last entry. I want to validate that M09401-07J2129-D24 (D39) exists in the column labeled ".25 46100" (C39) on the "material" worksheet. If it is there, I would like it to say "TRUE" in cell I39 of "shipped parts.

For the entry on line 37, I would want to search for 0947L-77944- in the column .375 12560-1 & validate it in I37.

I assume this would take a mix of IF & VLOOKUP functions, but I am lost on how to do it.

Originally Posted by NBVC

First, remove the trailing space from each cell in A1:E1 of the Material sheet.

then try formula in I2 of Shipping Parts sheet:

=IF(D2="","",ISNUMBER(MATCH(D2,INDEX(MATERIAL!$A:$E,0,MATCH(C2,MATERIAL!$A$1:$E$1,0)))))

copied down.

this will return a blank if column D is blank...

Thank you for the help. This looks like a start, but this seems to be just validating that the column heading (.25 46100, .25 12560-1,...) is on the "Material" worksheet.

For example if I changed D39 to read M09401-07J2129-D21 instead of M09401-07J2129-D24, I would expect I39 to be FALSE because the number does not exist under .25 46100 on the "MATERIAL" worksheet.

Originally Posted by NBVC

Forgot a 0 for the first Match()

Please Login or Register  to view this content.

You sir, are a life saver. Now the question is, do I continue on this path with Excell, or do I learn Access & do it the right way....