Adding vlookup formulas

[croweld89]

I'm sure that this problem has already been addressed, however, I could not find a post answering my question. Could someone please reply to this response with a link to the corresponding post. Thank you.

If the question has not already been asked the following are my formulas:

=(IF(ISNA(VLOOKUP($B88,'exec data'!$A$5:$O$51,6,FALSE)),0,VLOOKUP($B88,'exec data'!$A$5:$O$51,6,FALSE)))

+

(IF(ISNA(VLOOKUP($B88,'10 Reorg Data'!$A$5:$O$51,6,FALSE)),0,VLOOKUP($B88,'10 Reorg Data'!$A$5:$O$51,6,FALSE)))

Both equations work alone, however, I would like the resluts of both "summed" in the same cell. (i.e. the first equation results in 1 and the second equation results in 1, therefore 2 should be the answer 1+1=2)

Thank you for your time.

-Dave

[Aladin Akyurek]

On 2002-04-02 08:03, croweld89 wrote:
I'm sure that this problem has already been addressed, however, I could not find a post answering my question. Could someone please reply to this response with a link to the corresponding post. Thank you.

If the question has not already been asked the following are my formulas:

=(IF(ISNA(VLOOKUP($B88,'exec data'!$A$5:$O$51,6,FALSE)),0,VLOOKUP($B88,'exec data'!$A$5:$O$51,6,FALSE)))

+

(IF(ISNA(VLOOKUP($B88,'10 Reorg Data'!$A$5:$O$51,6,FALSE)),0,VLOOKUP($B88,'10 Reorg Data'!$A$5:$O$51,6,FALSE)))

Both equations work alone, however, I would like the resluts of both "summed" in the same cell. (i.e. the first equation results in 1 and the second equation results in 1, therefore 2 should be the answer 1+1=2)

Thank you for your time.

-Dave

Simply enter them as

=Formula1+Formula2

in one cell.

[wi_guy]

I just tested it and it worked fine for me....what result are you getting?

[croweld89]

I followed the logic on my formulas and found that my new spreadsheet (2nd sheet) was skewed. Simply adding the formulas did work.

Do you have any suggestions on how to simplify these formulas?

Thanks for your time.

-Dave

[IML]

There is a way to simplify your need to do the double lookup by using a countif statement against the first column of your critia. But for simplicity, I would just do your vlookups in two cells, say G1 and G2, and then add them with the formula
=SUMIF(G1:G2,"<>#n/a")

good luck

[Aladin Akyurek]

On 2002-04-02 08:42, croweld89 wrote:
I followed the logic on my formulas and found that my new spreadsheet (2nd sheet) was skewed. Simply adding the formulas did work.

Do you have any suggestions on how to simplify these formulas?

Thanks for your time.

-Dave

You could rewrite

=(IF(ISNA(VLOOKUP($B88,'exec data'!$A$5:$O$51,6,FALSE)),0,VLOOKUP($B88,'exec data'!$A$5:$O$51,6,FALSE)))

as

=IF(COUNTIF('exec data'!$A$5:$A$51,$B88),VLOOKUP($B88,'exec data'!$A$5:$O$51,6,0),0)

Further you could give names to relevant ranges. For example,

select the range $A$5:$A$51 in exec data, go the Name Box on the Formula Bar, type an intelligible name e.g., execLVALUES, and hit enter [ LVALUES from lookup values ].

Now you can use this name in the COUNTIF instead of longish 'exec data'!$A$5:$A$51.

Name the range 'exec data'!$A$5:$O$51, e.g., execLTABLE and use this name in VLOOKUP.

Hope this helps.

Aladin