Adding vlookup formulas

croweld89

New Member
Joined
Mar 5, 2002
Messages
33
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
 

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)
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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