if all you have is 3 columns of data and want to use excels lookups, you
could use the following in B1

IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!A:A,1,FALSE))),"Y ard
1",IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!B:B,1,FALSE))) ,"Yard
2",IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!C:C,1,FALSE))) ,"Yard 3")))


Or you could insert a row on sheet 2 (at Row2) and enter a vlookup function
in cell A2, then copy it across. Then, on Sheet1 B1 you could use the Index
and Match functions to search the results of the Vlookup functions on sheet2
and return the Yard number in Sheet2 Row 1.

Sheet 1

Cell B1 =INDEX(Sheet2!1:1,1,MATCH(A1,Sheet2!2:2,0))


Sheet 2

X =VLOOKUP(Sheet1!$A1,Sheet2!A3:A5000,1,FALSE) - then copy this formula
across for Yard2 and Yard3.


Yard 1 Yard 2 Yard 3
X
bus45 bus123 bus99
bus12 bus08 bus122
bus001 bus002 bus45
bus09


"nrussell" wrote:


I've not used Lookup before and having troble to get it to work with the
following table.

Sheet 1
A1 = bus123

B1 = Bus Yard

Sheet 2
Yard 1 Yard 2 Yard 3
bus45 bus123 bus99
bus12 bus08 bus122
bus001 bus002 bus45
bus09

The value to lookup is Cell A1 - $A$1 and I want the yard which the bus
is in to be entered in to B1. I don't think this can be done with only
lookup. Is there anyway to solve this using a function using IF ELSE?

-Joe


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