Help needed with nested if statement incorporating Vlookup

NeilF

Board Regular
Joined
Sep 4, 2007
Messages
100
Please can someone help me correct a formula, which I nearly have working, but not quite!

Column A in my worksheet contains some cells with people's names, some with dates, and some blanks. I am trying to create a formula which when I drag it down through a range, doesn't return #N/A for the rows containing a blank, or a date.

So, to explain further, sheet 2 contains a further table of data, with names in column A. I use Vlookup to extract the data I need from sheet 2 and pull it into sheet 1, depending on the person's name entered into column A of sheet 1.

One early example which works fine is as follows:-

=IF(E16="O",((VLOOKUP($A16,data,12,FALSE))),0)

This formula can be dragged down and works fine, because cell E16 will only contain a letter, where there is a person's name in column A.

The formula I am trying to correct is failing because one of the logical tests, is based on a value from within the sheet 2 table of data, and such a value can only be obtained if column A of sheet 1 contains a real name. If it contains a blank or a date, that test returns a #N/A value and my formula, which works in all other ways, returns #N/A, whereas I actually want it to return zero.

I will post my formula below, which looks horribly complicated though it does work essentially, but can anyone help me with this one final issue, please?

=IF(AND(VLOOKUP($A16,data,7,FALSE)>0,G16>0),(G16*$G$11*D16),IF(AND(VLOOKUP($A16,data,7,FALSE)=0,G16>0),((VLOOKUP($A16,data,12,FALSE)*G16*$G$11)),0))

Thanks

Neil
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
If it contains a blank or a date, that test returns a #N/A value and my formula, which works in all other ways, returns #N/A, whereas I actually want it to return zero.

Why not handle it with iferror()?

=iferror(___formulahere____,0)
 
Upvote 0
Try

For Excel 2003 and lower
=IF(ISERROR(VLOOKUP($A16,data,7,FALSE)),0,VLOOKUP($A16,data,7,FALSE))

For Excel 2007 and higher
=IFERROR(VLOOKUP($A16,data,7,FALSE),0)
 
Upvote 0
Folks

Many thanks for your replies. I had not come across ISERR or ISERROR or ISNA before...

... so I have read up about them, and I understand what is going on, but I am struggling to get the syntax right when incorporating one of these into my (already bloated!) formula! Would someone be able to help me with this, please?

To put what my formula is trying to achieve into words:-

If the value of column 7 in the lookup table is greater than zero, and G16 is greater than zero, return G16 * G11 * D16

If the value of column 7 in the lookup table equals zero and G16 is greater than zero, return G16 * G11 * the value in column 12 in the lookup table

Otherwise return zero.

My issue is when I drag this down to a row where there is no name in column A, and hence no value can be obtained from the lookup table.


BTW, I do realise that some of you suggested specific syntax, and you may have already given me the answer I need, but I have tried to incorporate it into my overall formula, and I haven't made it work yet!

Many thanks in anticipation.

Neil
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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