Multiple VLOOKUP / MATCH Help

[jws]

Hello,

I could not think of the search terms to find what I am looking for but ideally you can lend me a hand. I have multiple cities and I am trying to pull data from a large CSV table and I have two pictures attached.

• Summary is my result sheet that is a weekly rolling update
• CSV is a large file that builds from 2009 to current date

I need to use function to match the city, then if true, match the date with a lookup and populate the proper column.

Ex.
1) B1 = Philadelphia, PA, G1 = New York, NY
2) Then I need to pull the CSV data to match the date of May 20th to the exact cities event. I have both shown below with dummy data
a. I can do this with a normal VLOOKUP by sorting the CSV data and then updating the formula, but I would rather not do this as this data table will continue to grow and more cities will be added on an as needed basis

If anything else is needed please let me know, thanks for your time.

Summary

CSV Data

 

[WindsorKnot]

The easiest thing to do would be to use the sum array
I.E. SUM((range1=criteria)*range2=criteria2)*(datarange)) then type control shift enter

 

[jws]

Hello,

Thanks for the reply but I am not really following your answer. I will keep trying for now.

 

[jws]

Okay, I believe I have my results. I still did not get the formula from above worked out, but this works for me now so all is well – thanks.

Cell C3 in Summary
=IF(csv!$A:$A=$B$1, VLOOKUP($A3, csv!$B:$D, 2, FALSE), 0)

$B$1 = Philadelphia, PA (city reference field as more will be generated)

 

[Domenic]

See if this helps...

http://xl-central.com/lookup-multiple-criteria.html

 

[mitchv3]

Domenic,

That xl-central link is very helpful, but what if you have 2 matches you want to sum? For example, sum all of the A,X instances (without using a helper column).

Thanks,

 

[Domenic]

Domenic,

That xl-central link is very helpful, but what if you have 2 matches you want to sum? For example, sum all of the A,X instances (without using a helper column).

Thanks,

In that case, if you're using Excel 2007 or later, you could use the SUMIFS function. For prior versions, you could use SUMPRODUCT. Have a look at the following example...

http://xl-central.com/sum-multiple-criteria.html

 

[mitchv3]

So close! However, I have one more limitation.

What if we say my criteria is A and X. But my data is AKKKK and X. In this instance I want to sum all that begin with A.

 

[Domenic]

Try...

=SUMIFS(D2:D10,B2:B10,F2&"*",C2:C10,G2)

or

=SUMPRODUCT(--(LEFT(B2:B10,LEN(F2))=F2),--(C2:C10=G2),D2:D10)

 

[mitchv3]

That worked like a dream. Thank you very much!