Hi,
Please help me with looking up using multiple criterias, see attached file.
Thanks in advance.
Hi,
Please help me with looking up using multiple criterias, see attached file.
Thanks in advance.
Could you explain more what you are trying to do, instead of just saying "here's my file, you figure it out"
You could either use a helper column on each spreadsheet, which concatenates the fields you are using in the lookup, such as:
Formula:Please Login or Register to view this content.
which you could paste in cell G2 on Data and drag down, and cell F2 on the sheet for each month (then drag down). You could then lookup with this formula:
Formula:Please Login or Register to view this content.
(paste under the Jan column on the Data sheet and drag down).
Or, if you fancied an array formula (confirm by CTRL+SHIFT+ENTER, rather than ENTER), you could use this:
Formula:Please Login or Register to view this content.
E2=IFERROR(LOOKUP(2,1/((Jan!$A$2:$A$7=$A2)*(Jan!$B$2:$B$7=$B2)*(Jan!$C$2:$C$7=$C2)*(Jan!$D$2:$D$7=$D2)),Jan!$E$2:$E$7),"")F2=IFERROR(LOOKUP(2,1/((Feb!$A$2:$A$7=$A2)*(Feb!$B$2:$B$7=$B2)*(Feb!$C$2:$C$7=$C2)*(Feb!$D$2:$D$7=$D2)),Feb!$E$2:$E$7),"")Please Login or Register to view this content.
Try this and copy towards downPlease Login or Register to view this content.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
For January
in E2 and copy down
=SUMPRODUCT((Jan!A$2:A$7=A2)*(Jan!B$2:B$7=B2)*(Jan!C$2:C$7=C2)*(Jan!D$2:D$7=D2)*(Jan!E$2:E$7))
For February
in F2 and copy down
=SUMPRODUCT((Feb!A$2:A$7=A2)*(Feb!B$2:B$7=B2)*(Feb!C$2:C$7=C2)*(Feb!D$2:D$7=D2)*(Feb!E$2:E$7))
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
With SUMIFS
=SUMIFS(Jan!E:E,Jan!A:A,A2,Jan!B:B,B2,Jan!C:C,C2,Jan!D:D,D2)
and
=SUMIFS(Feb!E:E,Feb!A:A,A2,Feb!B:B,B2,Feb!C:C,C2,Feb!D:D,D2)
Also if you change the headers for month in E1 and F1 to Jan and Feb you can use just one formula
enter formula in E2, pull it to the right and then down
=SUMIFS(INDIRECT("'"&E$1&"'!E:E"),INDIRECT("'"&E$1&"'!A:A"),$A2,INDIRECT("'"&E$1&"'!B:B"),$B2,INDIRECT("'"&E$1&"'!C:C"),$C2,INDIRECT("'"&E$1&"'!D:D"),$D2)
Thanks everyone, I will add to your reps. Though, I have not gotten a chance to try out your suggestions yet.
@davesexcel: Sorry if my opening post lacked information. I thought it would be better to show what I am dealing with through a basic set-up in attached excel file, instead of trying to explain my issue.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks