Combining "IF"statement with "Vlookup"

  • Thread starter Thread starter Malcolm McMaster
  • Start date Start date
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

Thanks in advance.

Malcolm McMaster
 
Try:

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

Assuming A1 is the cell to recieve a value that will be looked up elsewhere.
 
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)
 
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.
 
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.
 
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 > >
Click to expand...

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.
 
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?
Click to expand...

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

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.
 
Back
Top