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

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

Hi Joe:

Or

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

Place this in B1.

This formula works if you don’t have more than 7 Yards.


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

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

Here's another way...

=INDEX(Sheet2!A1:C1,SUMPRODUCT((Sheet2!A2:C5=Sheet 1!A1)*(COLUMN(Sheet2!A2:C5)-COLUMN(Sheet2!A2)+1)))

I've noticed that bus45 is listed under Yard 1 and Yard 3. I'm
assuming it's a typo. If not, the formula would have to change.

Hope this helps!


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

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

Try the following...

B1:

=INDEX(Sheet2!A1:F1,MIN(IF(Sheet2!A2:F5=A1,COLUMN( Sheet2!A2:F5)-COLUMN(Sheet2!A2)+1)))

B2:

=OFFSET(INDIRECT("Sheet2!"&ADDRESS(MIN(IF(Sheet2!A 2:F5=A1,ROW(Sheet2!A2:F5))),MIN(IF(Sheet2!A2:F5=A1 ,COLUMN(Sheet2!A2:F5))))),0,1)

Both these formulas need to confirmed with CONTROL+SHIFT+ENTER, not
just ENTER.

Hope this helps!


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

ah so thats an array formula. Sorry I've not used them in the past so
this may sound dumb but would you have to confirm them each time you
open the workbook or just once before you protect them?

Thanks Domenic, sorry to keep bugging you but hey you can't learn
unless you ask :)

-Joe


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

The only times you'll need to confirm with CONTROL+SHIFT+ENTER are when
you first enter the formula and whenever you edit the formula.

Hope this helps!

nrussell Wrote:
ah so thats an array formula. Sorry I've not used them in the past so
this may sound dumb but would you have to confirm them each time you
open the workbook or just once before you protect them?

Thanks Domenic, sorry to keep bugging you but hey you can't learn
unless you ask :)

-Joe


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

Domenic Wrote:
Try the following...

B1:

=INDEX(Sheet2!A1:F1,MIN(IF(Sheet2!A2:F5=A1,COLUMN( Sheet2!A2:F5)-COLUMN(Sheet2!A2)+1)))

B2:

=OFFSET(INDIRECT("Sheet2!"&ADDRESS(MIN(IF(Sheet2!A 2:F5=A1,ROW(Sheet2!A2:F5))),MIN(IF(Sheet2!A2:F5=A1 ,COLUMN(Sheet2!A2:F5))))),0,1)

Both these formulas need to confirmed with CONTROL+SHIFT+ENTER, not
just ENTER.

Hope this helps!

Replace the second formula, B2, with the following...

=INDEX(Sheet2!A1:F5,MIN(IF(Sheet2!A1:F5=A1,ROW(She et2!A1:F5)-ROW(Sheet2!A1)+1)),MIN(IF(Sheet2!A1:F5=A1,COLUMN(S heet2!A1:F5)-COLUMN(Sheet2!A1)+1))+1)

...confirmed with CONTROL+SHIFT+ENTER. It eliminates the use of
OFFSET, INDIRECT, and ADDRESS. Two of which (OFFSET and INDIRECT) are
volatile functions.

Hope this helps!


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