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.

"Malcolm McMaster" wrote:

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

On Friday, April 11, 2008 7:15:00 PM UTC-4, mike wrote:
Try:

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

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

"Malcolm McMaster" wrote:

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

Thanks worked like a charm!

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" wrote in
message ...
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

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" wrote in
message ...
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

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" wrote in
message ...
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

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" wrote in message ... 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.

"Nidhi" 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))

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.

hai