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)

"Malcolm McMaster" <Malcolm...@discussions.microsoft.com> wrote in
message news:4EAB98C7-D701-468E...@microsoft.com...

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

"Malcolm McMaster" <Malcolm...@discussions.microsoft.com> wrote in
message news:4EAB98C7-D701-468E...@microsoft.com...

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

On Saturday, April 12, 2008 2:40:01 AM UTC+3, mike wrote:
> 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. > 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. > "Malcolm McMaster" <Malcolm...@discussions.microsoft.com> wrote in > message news:4EAB98C7-D701-468E...@microsoft.com... > >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 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? Please help me.

[joeu2004]

"Nidhi" <nidhe...@gmail.com> wrote:
> 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 (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))

[joeu2004]

Errata.... I wrote:
> 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.

[sachinku...@gmail.com]

hai

[mtim...@smcstudents.ca]

Thanks worked like a charm!