lookup problems

  • Thread starter Thread starter nrussell
  • Start date Start date
N

nrussell

I've not used Lookup before and having troble to get it to work with th
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 bu
is in to be entered in to B1. I don't think this can be done with onl
lookup. Is there anyway to solve this using a function using IF ELSE?

-Jo
 
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
 
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.
 
Yeah thanks they both work fine but JMB's seems like a more stream lin
approach to it and very much like what I was trying to do to star
with.

I did change the code to the following as it allows just the table t
be updated instead of having to update the code also. So if the title
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))),Sheet2!A1,IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!B:B,1,FALSE))),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)),"Yar
1",IF(SUMPRODUCT(--(Sheet2!B1:B5=Sheet1!A1)),"Yar
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?

-Jo
 
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!
 
Domenic said:
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!
Click to expand...

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!
 
Back
Top