if and vlookup formula

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

Trying to use:


=IF(ISERROR(VLOOKUP(16618,A1:D21,3,FALSE)),"Not
found",VLOOKUP(16618,A1:D21,4,FALSE))

If the value is not found in column 3 of the table, I'd like to have
it look to column 4. If still not found, return "Not Found".

This isn't behaving. I know it's simple. . .thoughts are appreciated.

Pete
 
Trying to use:
=IF(ISERROR(VLOOKUP(16618,A1:D21,3,FALSE)),
"Not found",VLOOKUP(16618,A1:D21,4,FALSE))

If the value is not found in column 3 of the table,
I'd like to have it look to column 4.  If still not
found, return "Not Found".
Click to expand...

=IF(ISERROR(VLOOKUP(16618,A1:D21,3,FALSE)),
IF(ISERROR(VLOOKUP(16618,A1:D21,4,FALSE)),
"Not found",""),"")

Returns the null string (appears blank) if found.
 
=IF(ISERROR(VLOOKUP(16618,A1:D21,3,FALSE)),
IF(ISERROR(VLOOKUP(16618,A1:D21,4,FALSE)),
"Not found",""),"")

Returns the null string (appears blank) if found.
Click to expand...

Thank you Joeu2004.
Need it to return the value found in col 3, or col 4 if found (in that
order). If it's not in either of those columns, need a 'Not Found"
returned.

Thanks for looking at it.
Pete
 
=IF(ISERROR(VLOOKUP(16618,A1:D21,3,FALSE)),
IF(ISERROR(VLOOKUP(16618,A1:D21,4,FALSE)),
"Not found",""),"")
Click to expand...
[....]
Need it to return the value found in col 3, or col 4
if found (in that order). If it's not in either of those
columns, need a 'Not Found" returned.
Click to expand...

That is easier to do in XL2007 and XL2010, to wit:

=IFERROR(VLOOKUP(16618,A1:D21,3,FALSE),
IFERROR(VLOOKUP(16618,A1:D21,4,FALSE),"Not found"))

In XL2003, I would recommend putting the two VLOOKUPs into helper
cells to limit the number of VLOOKUPs to 2. The following one-liner
requires 2 or 3 VLOOKUPs.

=IF(ISERROR(VLOOKUP(16618,A1:D21,3,FALSE)),
IF(ISERROR(VLOOKUP(16618,A1:D21,4,FALSE)),"Not found",
VLOOKUP(16618,A1:D21,4,FALSE)),VLOOKUP(16618,A1:D21,3,FALSE))

PS: You can shorten the XL2003 formula slightly by using MATCH in
place VLOOKUP. But it will not improve performance significantly. To
wit:

=IF(ISERROR(MATCH(16618,A1:A21,0)),
IF(ISERROR(MATCH(16618,A1:A21,0)),"Not found",
VLOOKUP(16618,A1:D21,4,FALSE)),VLOOKUP(16618,A1:D21,3,FALSE))
 
=IF(ISERROR(VLOOKUP(16618,A1:D21,3,FALSE)),
IF(ISERROR(VLOOKUP(16618,A1:D21,4,FALSE)),
"Not found",""),"")
Click to expand...
[....]
Need it to return the value found in col 3, or col 4
if found (in that order). If it's not in either of those
columns, need a 'Not Found" returned.
Click to expand...

Sorry: which crafting an "improved" solution, which I deleted from
Google Groups but others will see in other news archives, I realized
that I misread your original request in the first place.

I would suggest that you do the VLOOKUPs in helper cells to limit the
number of VLOOKUPs to 2.

In XL2007 and XL2010, the following one-liner results in 2 to 3
lookups.

=IFERROR(IF(VLOOKUP(16618,A1:D21,3,FALSE)<>"",
VLOOKUP(16618,A1:D21,3,FALSE),
IF(VLOOKUP(16618,A1:D21,4,FALSE)<>"",
VLOOKUP(16618,A1:D21,4,FALSE),"Not found")),"Not found")

In XL2003, the following one-liner results in 3 to 4 lookups:

=IF(ISERROR(MATCH(16618,A1:A21,0)),"Not found",
IF(VLOOKUP(16618,A1:D21,3,FALSE)<>"",
VLOOKUP(16618,A1:D21,3,FALSE),
IF(VLOOKUP(16618,A1:D21,4,FALSE)<>"",
VLOOKUP(16618,A1:D21,4,FALSE),"Not found")))

If you are assured that 16618 is in A1:A21, you can use the same
shortened formula in all Excel versions:

=IF(VLOOKUP(16618,A1:D21,3,FALSE)<>"",
VLOOKUP(16618,A1:D21,3,FALSE),
IF(VLOOKUP(16618,A1:D21,4,FALSE)<>"",
VLOOKUP(16618,A1:D21,4,FALSE),"Not found"))
 
PS....

Trying to use:
=IF(ISERROR(VLOOKUP(16618,A1:D21,3,FALSE)),
"Not found",VLOOKUP(16618,A1:D21,4,FALSE))

If the value is not found in column 3 of the table,
I'd like to have it look to column 4. If still not
found, return "Not Found".
Click to expand...

Need it to return the value found in col 3, or col 4
if found (in that order). If it's not in either of those
columns, need a 'Not Found" returned.
Click to expand...

I presume you understand that the VLOOKUP looks for 16618 only in
A1:A21, not in C1:C21 (column 3) and D1:D21 (column 4).

So when you said "the value" is not found in columns 3 and 4, I
presume you do not mean 16618, except by coincidence. I interpreted
"the value" to mean "a value".

If my presumptions are incorrect, you might have a fundamental
misunderstanding of how VLOOKUP works.

In that case, I suggest that you explain what is in A1:A21, C1:C21 and
D1:D21 and how that relates to the lookup value (16618). A concrete
example would help resolve descriptive ambiguities.
 
HI joeu2004
Thank you for your time and trouble.
To recap and clarify
Am looking in column A for 16618
If found, 1st place to look for a value to return is in column C. Some
of the data in column C is blank,
If a blank is found in column C, then the next step is to look in
column D, and return its contents. Some of the entries in column D
might be blank too.
If 16618 is not found in column A, or if columns C and D are both
blank, I'd like "Not Found" to be returned to the field with the
vlookup formula.

Thank you so much once again.
Pete
 
Joe:

Your first example on this page works great!

Thank you.

Pete
=IF(ISERROR(VLOOKUP(16618,A1:D21,3,FALSE)),
IF(ISERROR(VLOOKUP(16618,A1:D21,4,FALSE)),
"Not found",""),"")
Click to expand...
[....]
Need it to return the value found in col 3, or col 4
if found (in that order). If it's not in either of those
columns, need a 'Not Found" returned.
Click to expand...

Sorry:  which crafting an "improved" solution, which I deleted from
Google Groups but others will see in other news archives, I realized
that I misread your original request in the first place.

I would suggest that you do the VLOOKUPs in helper cells to limit the
number of VLOOKUPs to 2.

In XL2007 and XL2010, the following one-liner results in 2 to 3
lookups.

=IFERROR(IF(VLOOKUP(16618,A1:D21,3,FALSE)<>"",
VLOOKUP(16618,A1:D21,3,FALSE),
IF(VLOOKUP(16618,A1:D21,4,FALSE)<>"",
VLOOKUP(16618,A1:D21,4,FALSE),"Not found")),"Not found")

In XL2003, the following one-liner results in 3 to 4 lookups:

=IF(ISERROR(MATCH(16618,A1:A21,0)),"Not found",
IF(VLOOKUP(16618,A1:D21,3,FALSE)<>"",
VLOOKUP(16618,A1:D21,3,FALSE),
IF(VLOOKUP(16618,A1:D21,4,FALSE)<>"",
VLOOKUP(16618,A1:D21,4,FALSE),"Not found")))

If you are assured that 16618 is in A1:A21, you can use the same
shortened formula in all Excel versions:

=IF(VLOOKUP(16618,A1:D21,3,FALSE)<>"",
VLOOKUP(16618,A1:D21,3,FALSE),
IF(VLOOKUP(16618,A1:D21,4,FALSE)<>"",
VLOOKUP(16618,A1:D21,4,FALSE),"Not found"))
Click to expand...
 
Your first example on this page works great!
Click to expand...

Good to hear. One improvement....

I would suggest that you do the VLOOKUPs in helper cells
to limit the number of VLOOKUPs to 2.
Click to expand...

Actually, we can reduce the number of lookups to 1. Suppose X1 is:

=MATCH(16618,A1:A21,0)

If you have a column of such formulas, you can hide the column, if you
wish.

Then we can write:

=IF(ISERROR(X1),"Not found",
IF(INDEX(C1:C21,X1)<>"",INDEX(C1:C21,X1),
IF(INDEX(D1:D21,X1)<>"",INDEX(D1:D21,X1),"Not found")))

Besides being much more efficient (fewer lookups), the formula adjusts
automagically if you move column C or D.

It also has the advantage of being compatible with all versions of
Excel. That makes it easier to share the file, if the need arises.
 
Good to hear.  One improvement....



Actually, we can reduce the number of lookups to 1.  Suppose X1 is:

=MATCH(16618,A1:A21,0)

If you have a column of such formulas, you can hide the column, if you
wish.

Then we can write:

=IF(ISERROR(X1),"Not found",
IF(INDEX(C1:C21,X1)<>"",INDEX(C1:C21,X1),
IF(INDEX(D1:D21,X1)<>"",INDEX(D1:D21,X1),"Not found")))

Besides being much more efficient (fewer lookups), the formula adjusts
automagically if you move column C or D.

It also has the advantage of being compatible with all versions of
Excel.  That makes it easier to share the file, if the need arises.
Click to expand...

Excellent! very slick. I anoint you Sir joeu2004.

Thanks again.
Pete
 
Back
Top