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, ...
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
Seeing:
"You've entered too many arguments for this function."
Hi
Try:
=if(b99="","",VLOOKUP($B99,Acct_budget_lookup,11,FALSE))
Kind regards
PGC
To understand recursion, you must understand recursion.
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!!!
This is not clear to me
1.
I'm looking for "xxx", what numbers are you talking about?... 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.
So if "xxx" is not found for ex. in the second range the result is "". Is this correct?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).
3.
What kind of ranges do the acct-budget1/2/3 refer to? Rectangular ranges, vertical vector ranges?
Please clarify
Kind regards
PGC
To understand recursion, you must understand recursion.
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.
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
A B C D 97 98 Result 99 a 20 100 [Book1]Summary
A B C K L M 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
C D E M N O 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
Kind regards
PGC
To understand recursion, you must understand recursion.
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.
Like this thread? Share it with others