lookup problems

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

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))),"Yard
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.

Here's another way...

=INDEX(Sheet2!A1:C1,SUMPRODUCT((Sheet2!A2:C5=Sheet1!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!

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.

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!A2: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!

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

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!

Originally Posted by nrussell

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

Originally Posted by Domenic

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!A2: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(Sheet2!A1:F5)-ROW(Sheet2!A1)+1)),MIN(IF(Sheet2!A1:F5=A1,COLUMN(Sheet2!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!