matching multiple columns help with formula

[kishoremcp]

Hi,
I have a file where I have two sheets
1. PV
2. Account wise

I need a formula which should validate and give me an output in two columns.

If the track column in sheet PV is matching with the Service line and the corresponding acount name then it should check in sheet PV for the value which is either in Accrued or in LOS. If the data is in LOS then the data of LOS should be in the corresponding column of sheet Account wise and if it is in Accrued then it should give the value in Achieved column for respective service line and account name in the sheet Account Wise.

Example: in the sheet PV (row 29) it is EUC(Track), HD Supply, INC(Account Name) and the data is in LOS
now it should give that value of LOS (1160000) in the sheet "Account wise" as it is matching as (service line in Account wise with Track in sheet PV), (Account name account wise with sheet Account name in sheet PV) and the value of LOS I want in E5 of sheet Account wise.

So, if LOS it should be in LOS and if it is accrued then it should be in achieved cells of that matching cells

Like the same way i want for all


I hope i am clear.
Please help me

Thanks in advance...

[Ace_XL]

In D2 of 'Accountwise' tab use

Please Login or Register  to view this content.

Copy down and across

Note: Entries in 'PV' tab and 'Accountwise' tab are not identical.
For instance, in PV tab you have "HD SUPPLY, INC." whereas in Accountwise tab you have "HD SUPPLY INC. "
Please amend these accordingly

[kishoremcp]

The formula is working but the problem is i think it is case sensitive and i think i have to eliminate the spaces also which are already present. Please help me to ignore the case sensitive and also to remove the spaces and use the same kind of formuls...

But thanks for the formula....

[kishoremcp]

Any help please

[kishoremcp]

As it is very slowly calculating, can we make it faster using something line sumproeuct(countifs(........))

Please help me...
or can we replace this sumproduct with countifs

which ever is faster to calculate..

[kishoremcp]

any formula which will work fast and substitute the above requirement even if it is only countifs also thats ok.. plz help

[kishoremcp]

any help please

[Ace_XL]

Try

Please Login or Register  to view this content.