Help needed with nested if statement incorporating Vlookup

[NeilF]

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

 

[pplstuff]

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)

 

[BenMiller]

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)

 

[Robert Mika]

For Excel 03/07/10:
=IF(ISNA(VLOOKUP($A16,data,7,FALSE)),0,VLOOKUP($A16,data,7,FALSE))

 

[NeilF]

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