Creating IF/THEN VLOOKUP statements?

Thanks:  0
Likes:  0

# Thread: Creating IF/THEN VLOOKUP statements?

1. ## Creating IF/THEN VLOOKUP statements?

I have a master worksheet which is populated by data from other worksheets. I use vlookup to do this.

This is sort of a total sales spreadsheet. The Master worksheets will show how will a product has sold in certain markets. Each of the sub-worksheets will represent data in these markets.

So I'm using VLOOKUP to scan each data worksheet, and if there is a header that matches a header on the master worksheet, then it's supposed to take the dollar figure next to it and plop it into the master worksheet.

The equation looks like this:

=VLOOKUP(\$C6,'test'!\$B:G,3,FALSE)

This works just fine. It looks on worksheet 'test', find the row which matches the text in cell C6 in the master sheet, then pulls the dollar figure next to it.

However, sometimes there will be no data elements in the worksheet that match the text in cell C6.

For example, say cell C6 on the master worksheet says ' BOB,' . If worksheet 'test' has a cell that says 'BOB' and we sold \$10,000 worth of untis in 'BOB,' then the master spreadsheet will reflect that.

However, if the worksheet 'test' does not have any cells that say BOB, I get a #N/A error in the master spreadsheet. This may hapen because while we track sales for BOB, there may not have been any sales for BOB this quarter.

I was trying to work up an IF statement so that if there is no value, it would just put in the number 0.

=IF(VLOOKUP(\$C4,'test'!\$B:G,3,FALSE)<>"",VLOOKUP(\$C4,'28days'!\$B:G,3,FALSE),0)

But this doesn't work either.

Am I making any sense? Does anyone have any ideas on how to resolve this?

2. ## Re: Creating IF/THEN VLOOKUP statements?

Either

=IF(ISNA(SETV(VLOOKUP(\$C6,'test'!\$B:\$G,3,0))),0,GETV())

which requires the morefunc.xll add-in

or

=IF(ISNA(V(VLOOKUP(\$C6,'test'!\$B:\$G,3,0))),0,V())

which requires the following code in your workbook as a module:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

3. ## Re: Creating IF/THEN VLOOKUP statements?

Why not use simply:

=IF(ISERROR(VLOOKUP(\$C6,'test'!\$B:G,3,FALSE)),0,VLOOKUP(\$C6,'test'!\$B:G,3,FALSE))

It works for me. Am I missing something?

4. ## Re: Creating IF/THEN VLOOKUP statements?

Hi Corni:

Aladin's proposed solutions alleviate the need to do the VLOOKUP computation twice. A simple analogy woukd be ... why pay for something twice, if it has already been paid for once (provided we have kept the receipt for it.)

5. ## Re: Creating IF/THEN VLOOKUP statements?

Originally Posted by Corni
Why not use simply:

=IF(ISERROR(VLOOKUP(\$C6,'test'!\$B:G,3,FALSE)),0,VLOOKUP(\$C6,'test'!\$B:G,3,FALSE))

It works for me. Am I missing something?
This is perfect Corni!!
I was missing the ISERROR function.
Thank you so much!

6. ## Re: Creating IF/THEN VLOOKUP statements?

Originally Posted by Berine
This is perfect Corni!!
I was missing the ISERROR function.
Thank you so much!
If you are on Excel 2007 or later, you can also invoke...

=IFERROR(VLOOKUP(\$C6,'test'!\$B:G,3,0),0)

7. ## Re: Creating IF/THEN VLOOKUP statements?

Originally Posted by Aladin Akyurek
If you are on Excel 2007 or later, you can also invoke...

=IFERROR(VLOOKUP(\$C6,'test'!\$B:G,3,0),0)
Thanks for sharing this, Aladin! This is exactly what I needed!

8. ## Re: Creating IF/THEN VLOOKUP statements?

Originally Posted by Tulsi17
Thanks for sharing this, Aladin! This is exactly what I needed!
You are welcome.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•