vlookup

  • Thread starter Thread starter demolay
  • Start date Start date
D

demolay

can we use "or" in vlookup's "look up value" part?
i want to look for two different values. either one of them exists an
i want to check both of them and return the value according to this. i
there any way to do this?
i write =vlookup(a2 or b2,.......) but it doesnt work, of coure :
 
demolay
VLOOKUP can only look for one value. You could probably do two lookups and put them inside an IF statement or use a concatenation. What do you mean when you want to find two values. Are they in the same column or two different columns? If they are in the same column and both exist do you want to return on both values or only one

Regards
Mark Graesse
(e-mail address removed)

----- demolay > wrote: ----

can we use "or" in vlookup's "look up value" part
i want to look for two different values. either one of them exists an
i want to check both of them and return the value according to this. i
there any way to do this
i write =vlookup(a2 or b2,.......) but it doesnt work, of coure :
 
they are in different columns. i want to use two vlookup in a single
formula in short.
 
Mark said:
VLOOKUP can only look for one value. . . .
Click to expand...

Not so. E.g., if x and y are in A1:A10, then either of the following,
array entered, will return the corresponding values from Column B:

=VLOOKUP({"x","y"},A1:C10,2,FALSE)
=VLOOKUP({"x";"y"},A1:C10,2,FALSE)

The first will return them to two cells in a row, the second to two
cells in a column.

Alan Beban
 
i wanna check a value from a cell. in short, i want to check two value
in two different cells in a column. one of them exists, so i need t
use some thing x or y..
 
Try

=INDEX(Return_Range,MATCH(1,(1st_Lookup_Range=condition1)*(2nd_Lookup_Range=
condition2),0))

entered with ctrl + shift & enter might look something like

=INDEX($D$2:$D$20,MATCH(1,($A$2:$A$20="x")*($B$2:$B$20="y"),0))

or if you meant search for a value in a table where you lookup the
coordinates

=INDEX(Table,MATCH("y",topmost_row_of_Table,0),MATCH("x",leftmost_column_of_
Table,0))
 
demolay,
This might be what you are looking for:

=IF(ISNA(VLOOKUP(E1,A1:B5,2,0)),VLOOKUP(F1,A1:B5,2,0),VLOOKUP(E1,A1:B5,2,0))

A1:B5 - lookup table
E1 - first lookup value
F1 - second lookup value

This if statement will check if the first lookup value is in the table. If it isn't then it will lookup the second value, if it is then it will lookup the first value.

Good Luck,
Mark Graesser
(e-mail address removed)

----- demolay > wrote: -----

they are in different columns. i want to use two vlookup in a single
formula in short.
 
Alan
Isn't your array formula just a way of combining two seperate formulas? Each of which searches on one value

I believe the OP wants to get a return from the lookup if either of two values is entered in two seperate cells. Kind of like an OR function for the lookup value

Regards
Mark Graesse
(e-mail address removed)

----- Alan Beban wrote: ----

Mark Graesser wrote
VLOOKUP can only look for one value. . .
Click to expand...

Not so. E.g., if x and y are in A1:A10, then either of the following,
array entered, will return the corresponding values from Column B

=VLOOKUP({"x","y"},A1:C10,2,FALSE
=VLOOKUP({"x";"y"},A1:C10,2,FALSE

The first will return them to two cells in a row, the second to two
cells in a column

Alan Beba
 
they are in different columns. i want to use two vlookup in a single
formula in short.
Click to expand...

I'm not sure you understand what vlookup does. Given a value and an
array, VLOOKUP looks for that value in that array, and if it can't find
it, depending on the value of the range_lookup parameter, it either fails
(returns #N/A) or returns the closest value to it that doesn't go over.

If you want to look for either of two values, you need to use two
VLOOKUPs, and then an IF to select the appropriate one.
I'd use an additional cell for scratch space.

If you want to find the row number that has BOTH Value1 in column A and
Value2 in column B, assuming that neither column 1 nor column 2 had a
"+" character:

Put this in C1 and then fill down: =A1&"+"&B1
Then use
=VLOOKUP(Value1&"+"&Value2,C1:C10,1,FALSE)
 
Back
Top