Multiple Lookups and SumIf

[sasindiv]

Hi,

I have an excel sheet with three sheets.

Main Page- This sheet is where the formulas needs to be. It will have Names,Dates and the cells where we need to do a lookup.

Dump- This is the placeholder for the data. I need to fetch data from this sheet for the corresponding date and name with the main page. All is well till here.

But the problem comes in the Names being displayed in the Main Page and the Dump Sheet. The Names are not same.It is shortened in the main page.
For example

Main Page might have name as Russ. There will be no reference to Russ in Dump Sheet. It will have names like Russel Peters, Russel Mccabe etc...

I need to do a match for this.

To help in this matching, there is a sheet called as Reference. It provides the mapping between the names in Main page and Dump sheet.

So I need a formula which takes the name in the Main Page, gets the corresponding name in the dump sheet and does a match based on date for that name.

I have attached the XLS too.

I need this mainly in Google docs but if this is not possible in google docs i am ok with excel too.

Can someone please help me on this.

[vlady]

try

Please Login or Register  to view this content.

if above suits your need you could include the iferror statement.
=iferror(theforumlaabove,"")

[sasindiv]

Thanks for the reply vlady.

My Bad, i missed a point. When i say the names wont match there can be a case like this too.

Please Login or Register  to view this content.

Eventhough all the names start with Russel, the main page can have some entries with Russ and others can be different.

I have attached the modified excel sheet.

Sorry for the confusion caused

[vlady]

I have no idea based on your output since

RP = 10

you have 2 RP that's..

Russell Peters =15
Russel Parker =10

are your outputs correct?

[sasindiv]

Hi,

Only Russell Parker will come under RP, its value is 10. In this case there are two Russ.

Russell Peters is mapped to Russ, so value of Russ will be the sum of Russell Peters and Russel Mccabe.

Please let me know if this is unclear.

[vlady]

oops. I was not thinking at the reference tab sorry my bad.
I think I have to get sleep been 16 hours working.
members are looking now at this thread hope they can come up.
Sorry, if ever I will be looking to it if not yet solved, have to rest for a while.

[sasindiv]

Thanks vlady for your time I hope the thread will be solved sooner too

[benishiryo]

hi sasindiv. life would be much easier if you can fill in the blank cells of the "Reference" sheet with names. So E6 would be "Michael". if you have a lot to fill up, you can use this method:
1. select B4.*
2. click & drag the scroll bar to the end where the data of column B ends (say 100)
3. press & hold the SHIFT key & left click on the last cell in column B (B100). you should see them all selected
4. press CTRL + G -> Special -> Blanks -> OK. now only blanks are selected
5. do not click anywhere else. press the "=" sign
6. press the UP arrow key
7. hit CTRL & ENTER

with that, your formula in B4 of "Main Page" would simply be:

Please Login or Register  to view this content.

custom format cells as this to hide zeroes:
General;;

or if you don't wish to format cells like that, then:

Please Login or Register  to view this content.

if it's impossible to fill up the blank names, then maybe:

Please Login or Register  to view this content.

above is an array formula to be confirmed with CTRL + SHIFT + ENTER

[sasindiv]

Hi Benishiryo ,

Thanks for the detailed explanation.

I am ok with filling the blank cells in the reference sheet. And the formula you provided worked.

Thanks for helping me out.