Yeah thanks they both work fine but JMB's seems like a more stream line
approach to it and very much like what I was trying to do to start
with.

I did change the code to the following as it allows just the table to
be updated instead of having to update the code also. So if the titles
of the yards change it will now get the value of the correct cell ref.



IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!A:A,1,FALSE))),Sh eet2!A1,IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!B:B,1,FAL SE))),Sheet2!B1,IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!C :C,1,FALSE))),Sheet2!C1)))


Flintstone did you mean:

=IF(SUMPRODUCT(--(Sheet2!A1:A5=Sheet1!A1)),"Yard
1",IF(SUMPRODUCT(--(Sheet2!B1:B5=Sheet1!A1)),"Yard
2",IF(SUMPRODUCT(--(Sheet2!C1:C5=Sheet1!A1)),"Yard 3","missing")))

This works well too but why does it only work with up to 7 columns?

-Joe


--
nrussell
------------------------------------------------------------------------
nrussell's Profile: http://www.excelforum.com/member.php...o&userid=23731
View this thread: http://www.excelforum.com/showthread...hreadid=374639