Vlookup does not work, need help please
Posted by Joan P. on August 02, 2000 6:57 AM
I have this spreadsheet:
A1 abc
A2 def
A3 ghi
A4 jkl
A5 mno
A6 pqr
B1 3
B2 5
B3 blank
B4 blank
B5 54
B6 blank
C1 jkl
C2 pqr
C3 blank
C4 blank
C5 blank
C6 blank
D1 33
D2 23
D3 blank
D4 blank
D5 blank
D6 blank
E1 abc
E2 def
E3 mno
E4 jkl
E5 pqr
E6 blank
In F1:F6 I try to use a VLOOKUP formula but it does not work properly. I use =IF(ISERROR(VLOOKUP(E1,$A$1:$B$6,2,FALSE)),"",IF(NOT(ISERROR(VLOOKUP(E1,$A$1:$B$6,2,FALSE))),VLOOKUP(E1,$A$1:$B$6,2,FALSE),IF(ISERROR(VLOOKUP(E1,$C$1:$D$6,2,FALSE)),"",VLOOKUP(E1,$C$1:$D$6,2,FALSE))))
The number of rows in column A B C and D are will vary that's why I do not use link or other aproach. Thanks
Posted by Celia on August 02, 0100 7:23 AM
Joan
I tried the formula with your sample data and it seems to work, but it depends on what you are trying to do.
Please clarify.
Celia
Posted by Joan on August 02, 0100 7:28 AM
Celia, I get zero for instead of 33 23 for column C and D
Posted by Celia on August 02, 0100 8:06 AM
Joan
Try this :-
=IF(ISNA(VLOOKUP(E1,$A$1:$B$6,2,FALSE)),IF(ISNA(VLOOKUP(E1,$C$1:$D$6,2,FALSE)),"",(IF(VLOOKUP(E1,$C$1:$D$6,2,FALSE)="","",VLOOKUP(E1,$C$1:$D$6,2,FALSE)))),(IF(VLOOKUP(E1,$A$1:$B$6,2,FALSE)="",IF(ISNA(VLOOKUP(E1,$C$1:$D$6,2,FALSE)),"",(IF(VLOOKUP(E1,$C$1:$D$6,2,FALSE)="","",VLOOKUP(E1,$C$1:$D$6,2,FALSE)))),VLOOKUP(E1,$A$1:$B$6,2,FALSE))))
If the rows are variable, it would be advisable to put in your formula the ranges as $A$1:$B$10000 and $C$1:$D$10000. Then you wont have to keep changing the formula.
Also you could name these ranges and use the names in the formula instead - it would be easier to read and if you did have to change the ranges, it would be easier to change the named ranges than to change the formula.
Celia
Posted by Joan P. on August 02, 0100 8:40 AM
