Creating IF/THEN VLOOKUP statements?

holee

New Member
Joined
Dec 15, 2002
Messages
8
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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
 
Upvote 0
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?
 
Upvote 0
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.)
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top