IF statement, AND & Vlookup on 3 condition that's not in numeric

[JulieB]

Hi,



I read some of the thread in Excel forum mostly explains IF statement, AND & Vlookup when the conditions are numeric. I'm not sure if my request is possible. Would appreciate if someone can advise.

Here's the screen shot and request queries.
2 sheets - Revenue and Summary
Summary worksheet.JPG

Revenue worksheet.JPG

I need to find out the revenue $ [G2] for SAP No [Cell A2] according to Fiscal Period - FY12 Q1 [Cell C2] in Summary sheet from Revenue sheet which contains list of Revenue arranged according to Fiscal Period and SAP No.

I tried IF statement and Vlookup and/or add on AND conditions but not feasible. Appreciate advise there's any alternative solution apart from checking one by one as my data is huge.

Many Thanks!

[Pecoflyer]

Hi and welcome
could you please post a sample sheet instead of a picture? It's much easier to work on - Thx

[JulieB]

Hi Pecoflyer,

I'm new to this forum. I don't know how to attached the sample sheet.
Can you advise me please.

Thanks,
Julie

[JulieB]

I have attached the excel worksheet.
Hopefully it is viewable.

Thanks,
Julie

[SteveWcg]

Originally Posted by JulieB

Hi,

I read some of the thread in Excel forum mostly explains IF statement, AND & Vlookup when the conditions are numeric. I'm not sure if my request is possible. Would appreciate if someone can advise.

Here's the screen shot and request queries.
2 sheets - Revenue and Summary
Attachment 4023

Attachment 4024

I need to find out the revenue $ [G2] for SAP No [Cell A2] according to Fiscal Period - FY12 Q1 [Cell C2] in Summary sheet from Revenue sheet which contains list of Revenue arranged according to Fiscal Period and SAP No.

I tried IF statement and Vlookup and/or add on AND conditions but not feasible. Appreciate advise there's any alternative solution apart from checking one by one as my data is huge.

Many Thanks!

I'm sure one of the others will have a more sensible answer

However try this

Summarise the data in Revenue as there are two lines for 1000730 for FY12 Q1
Then as a workaround, this is where the experts will shone, use in G2 =IF(ISNA(VLOOKUP(A2&C2&"Total",Revenue!$D$1:$F$39, 3,FALSE)),0,VLOOKUP(A2&C2&"Total",Revenue!$D$1:$F$ 39,3,FALSE))
and copy down.

Gives the correct answer

[Pecoflyer]

Does the attached help ?

=SUMPRODUCT((Revenue!$A$2:$A$21=Summary!A2)*(Revenue!$C$2:$C$21=TRIM(Summary!C2))*Revenue!$E$2:$E$21)

Be aware that some of the fiscal period data seems to have trailing edges.
That is why the trim function was used

[JulieB]

Thanks SteveWcg and Pecoflyer for the quick reply. I was wondering why both of you gave me the sum of 2 quarters AR numbers. Apparently the summary data given is incorrect. The fiscal period is arranged in quarterly manner. Apologies for this error made. I have reattached the worksheet and highlighted the Summary sheet and Revenue sheet for FY12Q1 AR numbers in yellow while FY12Q2 AR numbers in blue. Could you advise how to formulate the condition to get the Revenue number from Revenue sheet.

Thanks a lot!

[Pecoflyer]

Like this ?

[JulieB]

Hi Pecoflyer,

It doesn't works for my work with the formula given.

[Pecoflyer]

What do you mean by " It doesn't work" ?

[BobBridges]

LOL. Peco, I once was talking to a user over the phone:

Me: So did you try what I gave you?
User: Yeah, but it didn't work.
Me: "Didn't work"? What, exactly did it do.
User: Nothing.
Me: No, it didn't do nothing, it did something. Did the screen go black? Did the computer start to smoke? It probably displayed an error message?
User: Oh, yeah, an error message.
Me: What did the error message say?
User: Oh, it said some damn thing.

He was a friend of mine, and I like him. But really, he'd heard from me on this subject often enough already that he should have known better than to call me without looking at the error message, just to tell me "it didn't work".

JulieB, you gotta give symptoms or the diagnostician can't help. You can't tell the doctor simply "I'm sick" and expect him to know whether he needs to give you some aspirin, remove your appendix or refer you to an oncologist.

[Pecoflyer]

Nice one Bob

Sometimes frustrating one has to drag information out of an OP, the latter forgetting he gets FREE help provided by people working on their spare time

[SteveWcg]

Originally Posted by JulieB

Hi Pecoflyer,

It doesn't works for my work with the formula given.

You need to go google Product() as an array vs Range as it confuses the buggery out of me every time I try to use it.

To make it work on the work spreadsheet you need to set the array, can't remember how, or use the vlookup trick of concatenating the key in the target sheet.
There's a way to do a backwards vlookup but forgotten how.

[macropod]

Originally Posted by JulieB

Thanks SteveWcg and Pecoflyer for the quick reply. I was wondering why both of you gave me the sum of 2 quarters AR numbers. Apparently the summary data given is incorrect. The fiscal period is arranged in quarterly manner.

You should be able to use:

=SUMPRODUCT((Revenue!A:A=A2)*(Revenue!C:C=C2)*(Revenue!D:D=D2),Revenue!E:E)

or, if you want to be able to copy the formula to other columns as well:

=SUMPRODUCT((Revenue!$A:$A=Summary!$A2)*(Revenue!$C:$C=Summary!$C2)*(Revenue!$D:$D=Summary!$D2),Revenue!E:E)

in Summary!G2 and copy down as far as needed. However, you have a data entry error in Summary!C2, which has a space after 'FY12 Q1', which is why Pecoflyer had to add the TRIM statements. This highlights the crucial issue that data entry errors can invalidate the results. As they used to say: garbage in : garbage out.

[macropod]

Originally Posted by SteveWcg

You need to go google Product() as an array vs Range as it confuses the buggery out of me every time I try to use it.

To make it work on the work spreadsheet you need to set the array, can't remember how, or use the vlookup trick of concatenating the key in the target sheet.
There's a way to do a backwards vlookup but forgotten how.

If you study the solution posted by Pecoflyer, you'll see there is no use of PRODUCT(), or array formulae, or VLOOKUP(), because none of them is necessary.