# Combining "IF"statement with "Vlookup"

M

#### Malcolm McMaster

I have a data table in a worksheet that uses the vlookup to match a unique
value from a cell and populate various other cells with data retreived.
The problem is that the formula is active and any blank lookup value returns
the usual #NA throughout the data sheet. I thought I might be able to combine
an "IF" cell is not empty then Vlookup.
Can any body help with this ? or another alternative

Malcolm McMaster

M

#### mike

Try:

=IF(A1="","",VLOOKUP(whatever you need to lookup))

Assuming A1 is the cell to recieve a value that will be looked up elsewhere.

B

#### Bob Phillips

Just use

=IF(ISNA(vlookup_formula),"",vlookup_formula)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

A

#### Alan

Not entirely sure I understand.
If you mean the cell that contains the value the VLOOKUP is searching for is
blank, in this example D2, then
=IF(D2<>"",VLOOKUP(D2,A1:B7,2,FALSE),"")
If you mean the cell in the data table the VLOOKUP finds is blank then,
=IF(ISNA(VLOOKUP(D2,A1:B8,2,FALSE)),"",VLOOKUP(D2,A1:B8,2,FALSE))
If your using 2007 I believe there is a function to do this easier, (I'm on
2003)
Regards,
Alan.

M

#### mike

In 2007 you could also use IFERROR(), which is what I think Alan is refering
to.

=IFERROR(VLOOKUP(),"")

Also, Alan is correct in that it depends on what it is that is left blank.
My original suggestion assumed that the entry cell is what is blank.

N

#### Nidhi

In 2007 you could also use IFERROR(), which is what I think Alan is refering to.=IFERROR(VLOOKUP(),"")Also, Alan is correct in that it depends on what it is that is left blank. My original suggestion assumed that the entry cell is what is blank."Alan" wrote:> Not entirely sure I understand. > Ifyou mean the cell that contains the value the VLOOKUP is searching for is > blank, in this example D2, then > =IF(D2<>"",VLOOKUP(D2,A1:B7,2,FALSE),"") > If you mean the cell in the data table the VLOOKUP finds is blank then, > =IF(ISNA(VLOOKUP(D2,A1:B8,2,FALSE)),"",VLOOKUP(D2,A1:B8,2,FALSE)) > If your using 2007 I believe there is a function to do this easier, (I'm on> 2003)> Regards,> Alan. > "Malcolm McMaster" <[email protected]> wrote in > message news:[email protected] > >I have a data table in a worksheet that uses the vlookup to match a unique > > value from a cell and populate various other cells with data retreived. > > The problem is that the formula is active and any blank lookup value > > returns > > the usual #NA throughout the data sheet. I thought I might be able to > > combine> > an "IF" cell is not empty then Vlookup. > > Can any body help with this ? or another alternative> > > >Thanks in advance.> >> > Malcolm McMaster > >

Sir's, I have the same issue, but something more complicated for me. i havetwo sheets in a work book,
sheet1- Item, Brand, price.
Sheet2 -Item, Brand, empty price column.
How to run vlookup to match the 2 entities in sheet 2 and match with sheet 1 and pull the exact price? Please help me.

J

#### joeu2004

Nidhi said:
i have two sheets in a work book,
sheet1- Item, Brand, price.
Sheet2 -Item, Brand, empty price column.
How to run vlookup to match the 2 entities in sheet 2
and match with sheet 1 and pull the exact price?

I don't see how this is related to the previous discussion or the quoted
response. And in any case, it would be better to submit a new question
instead of "responding" to a 5-year-old discussion.

Assuming that Item is in A2:A1000, Brand is in B2:B1000, and Price is in
C2:C1000, you might use the following array-entered formula (press

=VLOOKUP(A2&B2, Sheet1!\$A\$2:\$A\$1000&Sheet1!\$C\$2:\$C\$1000, 3, FALSE)

But I do not recommend that because it is inefficient and because
array-entered formulas are prone to human error: pressing just Enter by
mistake sometimes __seems__ to work (not really!) instead of producing an
Excel error.

It would be better to add a 4th column in Sheet1 (D21000) with formulas of
the form =A2&B2.

Then use the following normally-entered formula (just press Enter as usual):

=INDEX(Sheet1!\$C\$2:\$C\$1000, MATCH(A2&B2, Sheet1!\$D\$2:\$D\$1000, 0))

J

#### joeu2004

Errata.... I said:
Assuming that Item is in A2:A1000, Brand is in B2:B1000, and Price is
in C2:C1000, you might use the following array-entered formula (press
=VLOOKUP(A2&B2, Sheet1!\$A\$2:\$A\$1000&Sheet1!\$C\$2:\$C\$1000, 3, FALSE)

=INDEX(Sheet1!\$C\$2:\$C\$1000, MATCH(A2&B2,
Sheet1!\$A\$2:\$A\$1000&Sheet1!\$B\$2:\$B\$1000, 0))

But again: that is not really recommended for the reasons noted previously.

S

hai

M

#### mtimpano

Try:

=IF(A1="","",VLOOKUP(whatever you need to lookup))

Assuming A1 is the cell to recieve a value that will be looked up elsewhere.

Thanks worked like a charm!