# embedded formula

This is a discussion on embedded formula within the Excel Questions forums, part of the Question Forums category; Hi, I am trying to create a formula that reads: If cell "b99" is blank, then leave it blank, otherwise, ...

1. ## embedded formula

Hi,

I am trying to create a formula that reads:

If cell "b99" is blank, then leave it blank, otherwise, look up cell "b99" in the defined range of "Acct_budget_lookup,give me the value in column 11, for an exact match.

My formula (which isn't working) is:
=if(b99,"","",VLOOKUP(\$B99,Acct_budget_lookup,11,FALSE))

Could anyone tell me what I am doing wrong?

Thanks
Darcy

2. ## Re: embedded formula

Originally Posted by darcya
Hi,

I am trying to create a formula that reads:

If cell "b99" is blank, then leave it blank, otherwise, look up cell "b99" in the defined range of "Acct_budget_lookup,give me the value in column 11, for an exact match.

My formula (which isn't working) is:
=if(b99,"","",VLOOKUP(\$B99,Acct_budget_lookup,11,FALSE))

Could anyone tell me what I am doing wrong?

Thanks
Darcy
What error are you seeing?

3. ## Re: embedded formula

Seeing:

"You've entered too many arguments for this function."

4. ## Re: embedded formula

Hi

Try:

=if(b99="","",VLOOKUP(\$B99,Acct_budget_lookup,11,FALSE))

5. ## Re: embedded formula

That worked awesome - thank you!

Trying to get tricky... and probably not a good idea but is there any way to add the following to the formula:

1. Would like to be able to look for the what is in B99 in more than 1 defined range. Is this possible.

meaning, look for this "xxx" in defined range "acct-budget1", also in "acct-budget2", also in acct-budget3) and sum the numbers that you find?

2. If you can't find "B99" (or "xxx") in the defined range (or in any of the defined ranges), return a blank (instead of #N/A).

I'm way out of my comfortage area now and appreciate any and all help! Thanks!!!

6. ## Re: embedded formula

This is not clear to me

1.

... look for this "xxx" in defined range "acct-budget1", also in "acct-budget2", also in acct-budget3) and sum the numbers that you find?
I'm looking for "xxx", what numbers are you talking about?

2.

If you can't find "B99" (or "xxx") in the defined range (or in any of the defined ranges), return a blank (instead of #N/A).
So if "xxx" is not found for ex. in the second range the result is "". Is this correct?

3.

What kind of ranges do the acct-budget1/2/3 refer to? Rectangular ranges, vertical vector ranges?

7. ## Re: embedded formula

Let me try to explain better...

I am working a budget. Each project has it's own spreadsheet (tab). I have another where I am looking to basically summarize.

My defined range goes from B37:AW131; each column signifies monthly data (January hours, cost, Revenue, followed by February data, etc.)

So I would want a formula that says find (on the project sheets) the account listed in column B on this sheet (summary), and bring me back the number in column {11}. Then I would have another for February that may equate to column {15}, etc.

Don't know if this helps clarify what I am trying to do?

Thanks.

8. ## Re: embedded formula

I'm not yet sure that I understand.
This is an example of what I understood:

- you have 2 projects,

. 1 in worksheet Prj1 in range ='Prj1'!\$B\$37:\$AW\$131
. 1 in worksheet Prj2 in range ='Prj2'!\$D\$27:\$AY\$121

- In the Summary sheet you want to add the values in column 11 of those ranges when column 1 is equal to the value of Summary!B99

ABCD
97
98 Result
99 a20
100
[Book1]Summary
ABCKLM
36
37 a 1
38 b 2
39 c 3
40 a 4
41 b 5
42 c 6
43 a 7
44 b 8
45 c 9
46
[Book1]Prj1
CDEMNO
26
27 a 1
28 b 2
29 c 3
30 c 4
31 c 5
32 c 6
33 a 7
34 b 8
35 c 9
36
[Book1]prj2

 Addr Formula [Book1]Summary C99 =SUMIF(INDEX(acct_budget1,0,1),B99,INDEX(acct_budget1,0,11)) +SUMIF(INDEX(acct_budget2,0,1),B99,INDEX(acct_budget2,0,11))

 Names Name Refers to Workbook acct_budget1 ='Prj1'!\$B\$37:\$AW\$131 Book1 acct_budget2 ='prj2'!\$D\$27:\$AY\$121 Book1

9. ## Re: embedded formula

This is awesome and exactly what I need!! Thank you so much!!

To take it a step further, what if I add data to Summary a99 that I want to be a criteria also? Meaning, I want it to sum the defined ranges if the first column =a99 and the second column = b99? Is my only way to concatenate these out of sight?

Thank you so very much!! I do apologize for my delay in thanks - I was pulled off this for something else for a while and just tried it tonight.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•