How to use VLOOKUP to retrieve data based on a "Name" & "Date" query

[vangamut]

Hello,

Unfortunately I cannot seem to be able to do a VLOOKUP to match two criteria: Name and Date.

In the file attached I need to add a formula under the "Main" sheet that fetches the number value of "Phone Productivity" from the "Phone Productivity" sheet - and the same for "Email Productivity".

Is there a formula that enables me to acquire these productivity values based on "Date" and "Agent" at the same time?

Thanks in Advance.

[AliGW]

In C2:

=LOOKUP(2,1/('PHONE PRODUCTIVITY'!$A$2:$A$13=MAIN!A2)*('PHONE PRODUCTIVITY'!$B$2:$B$13=MAIN!B2),'PHONE PRODUCTIVITY'!$C$2:$C$13)

In D2:

=LOOKUP(2,1/('EMAIL PRODUCTIVITY'!$A$2:$A$13=MAIN!A2)*('EMAIL PRODUCTIVITY'!$B$2:$B$13=MAIN!B2),'EMAIL PRODUCTIVITY'!$C$2:$C$13)

[KOKOSEK]

Into C2 on Main tab:

Please Login or Register  to view this content.

into D2 on Main tab:

Please Login or Register  to view this content.

drag both formulas down as needed.


p.s. You can also use array formulas like this (for phone):

Please Login or Register  to view this content.

(to accept with CSE) but I don't see the point.

[Richard Buttrey]

Hi,

This is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

That said it would be possible but why struggle with formulae when a Pivot Table with some slicers to filter and analyse it would be simpler, more elegant and useful.

See attached. I've reformatted your Data and added a Pivot table sheet.

[vangamut]

Thanks a bunch, I think this formula is the easiest for me to understand. This was a much simplified example, I now have to apply this logic to a different file.

[KOKOSEK]

Richard, you are right. It is good practice to use pivot tables in the case like this, but, to be honest, for so simple data (of course if, as you said, sample is not representative), I don't see the point to use Pivot, if 2 simple SUMIFS formulas do the job.

[vangamut]

Thank you Richard for your input!

What I am trying to do is to build a Pivot Table from a Raw Data set which is composed of data from multiple sources.
I was looking for a formula I could use to help assemble these. KOKOSEK's formula worked wonders, tried and tested. Cheers!

[vangamut]

Thanks AliGW, I need more time to understand the logic of "Lookup" (as opposed to VLookup). Cheers!

[AliGW]

It's dead simple. Think of it as a formulaic representation of a lookup table:

=LOOKUP(lookup_value,lookup_array,result_array)

[KOKOSEK]

Am I incorrectly applied AliGW's formulas or it giving wrong results?

edit: found double open/closed brackets missing (were singles)

Please Login or Register  to view this content.