Combining "IF"statement with "Vlookup"

[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

Thanks in advance.

Malcolm McMaster

 

[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.

 

[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)

 

[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.

 

[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.

 

[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.

 

[joeu2004]

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
ctrl+shift+Enter instead of just Enter):

=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))

 

[joeu2004]

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
ctrl+shift+Enter instead of just Enter):
=VLOOKUP(A2&B2, Sheet1!$A$2:$A$1000&Sheet1!$C$2:$C$1000, 3, FALSE)

My bad! Use the following array-entered formula instead (press
ctrl+shift+Enter instead of Enter):

=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.

 

[sachinkumar.1226]

hai

 

[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!