Okay I have this working now and I understand JMB's formula but I'm now
trying to work out how to get the cell ref from the target array. The
code just looks to see if there is a match in the target columns and
returns a true / false value to progress though the logic statments. So
if cell A1 = bus123 the result is Sheet2!B1 this is correct column but
how do I find the correct row. The idea is to find the lookup target
cell ref of B2 and display an offset result for that result.

So the new table would be something like ;

Sheet 2
A B C D E F
1Yard 1 on duty Yard 2 on duty Yard 3 on duty
2bus45 repair bus123 repair bus99 private
3bus12 on duty bus08 other bus122 on duty
4bus001 on duty bus002 other bus008 other
5bus09 on duty

Sheet 1
A1 = bus123
B2 = offset result of above to the right to give status. (on
duty/repair)
B1 = bus yard

I've tried using INDEX / MATCH and LOOKUP / OFFSET and keep getting ref
or value errors. It's driving me mad trying to figure this out.


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