Excel VLOOKUP Problem

[Ben1637]

Hi Everybody:

How do I change this formula so if an #n/a is returned in one of the lookups, it will still sum the other lookup piece?
=SUM(VLOOKUP($J6,$A$5:$G$16,2,FALSE),VLOOKUP($J6,$A$24:$I$37,2,FALSE))

Any help you can provide would be greatly appreciated!

Thank you, in advance....

 

[AnotherHiggins]

=IF(ISNA(VLOOKUP($J6,$A$5:$G$16,2,FALSE)),VLOOKUP($J6,$A$24:$I$37,2,FALSE),IF(ISNA(VLOOKUP($J6,$A$24:$I$37,2,FALSE)),VLOOKUP($J6,$A$5:$G$16,2,FALSE),SUM(VLOOKUP($J6,$A$5:$G$16,2,FALSE),VLOOKUP($J6,$A$24:$I$37,2,FALSE))))

If you want to look for any error, use ISERROR instead of ISNA.

NOTE: This does NOT account for the situation where both vlookups return an error.

John

That which does not kill me postpones the inevitable.

 

[DaleWatson123321]

Ben,

Here's another option. Same result, but slightly shorter formula (even without the use of range names).

=SUM(IF(ISNA(VLOOKUP(val,tbl1,2,FALSE)),0,VLOOKUP(val,tbl1,2,FALSE)), IF(ISNA(VLOOKUP(val,tbl2,2,FALSE)),0,VLOOKUP(val,tbl2,2,FALSE)))

Please note that I've used "range names" instead of the cell coordinates. As an instructor, I would strongly recommend that if your instruction includes Excel, to highly recommend (teach) your students to get in the habit of using range names. They are very useful when creating formulas. And they are particularly useful when students or anyone gets into writing VBA code.

If cell coordinates are used in VBA code, then the cell references will remain static, unless and until manually changed. In contrast, when range names are used, the VBA code will almost always NOT require changing - only in exceptional situations. And code can more easily be written to reference and/or modify the range name coordinates when required.

Internally, Excel maintains a "link" between the range names created and the cell coordinates. Such links are maintained when common changes like the following are made: inserting/deletion or rows/columns, moving data from place to place. The range name links are actually maintained when moving data from worksheet to worksheet.

Hope this helps.

Regards, Dale Watson dalwatson@gov.mb.ca

 

[Ben1637]

That's fantastic...thank you very much for your help!