Multiple V-Lookup

[ctrapper]

Not sure if the title matches what I am needing.. if not I can correct. I am trying to accomplish the following. I have a spreadsheet that contains a list of cities in Column A, a Date in Column C, and a list of cities in Column E, and a numerical value in column AV. Here is what I am trying to do, and go down the page starting in Column AW, Row 3. Here is what I am needing. I'll use row 1 for my example. ATL is Col A, 10/27/2010 is in Col C, and MEM is in Col E. What I need to do is look in Column E for MEM, got to Col A and find MEM for the same 10/27/2010 Date, and return the value from Col AV, to Column AW in Row 3.

Did I explain well enough?

Note: There are multiple cities listed in A and E. So I want to match the cell rather than call each one out specifically in the formula if that makes sense?

[6StringJazzer]

I'll use row 1 for my example. ATL is Col A, 10/27/2010 is in Col C, and MEM is in Col E. What I need to do is look in Column E for MEM, got to Col A and find MEM for the same 10/27/2010 Date, and return the value from Col AV, to Column AW in Row 3.

You said you need to look in column E for MEM, but you have just said that in the row you're talking about MEM is in column E. So where do you need to look? Also, you said that you want the value from column AV put into column AW in row 3, but you started out saying we were looking at row 1. How did row 3 get into the picture?

It might help to back off the row & column explanation and explain what real-world thing you are trying to accomplish first.

[ctrapper]

Sorry let me re-arrange my wording..I see where I confused things.. I looked at my Spreadsheet again. Ok in Row 3, I have ATL in Col A, 10/27/2010 in Col C, MEM in Col E, and the numerical value 9.2 in Col AV. MEM appears in COL A with that same date, and a numerical value in Col AV, I want to be able to move that numerical data up to Row 3, and place it in Col AW.

Me real world explanation. I did a statistical analysis for each city, and put my final result in Col AV. I now want to compare 2 cities on identical dates. In my explanation ATL was 9.2 on 10/27/10, I want to find MEM in Col A for 10/27, along with the value in AV, and move it to the Col AW in Row 3.

[DonkeyOte]

For the sake of simplicity / efficiency you should create a "key", eg:

AX3:
=$A3&"@"&$C3
copied down

Then, assuming the combination for city & date will appear once only (if at all) use a SUMIF in AW

AW3:
=SUMIF($AX:$AX,$E3&"@"&$C3,$AV:$AV)

If you don't use the helper the calculation will be far less efficient.

If you have multiple instances of city & date then revert SUMIF to INDEX & MATCH but you will need to add a handler if we assume the combination might not always exist.

If in doubt post a sample file.

[ctrapper]

Thanks Donkey... getting the following in AX3.. tried formatting the column to date but it's not working.. Column C is formatted for date, any suggestions?

ATL@40480

[DonkeyOte]

The value you show is fine - in XL dates are just serial numbers (the cell format dictates whether you see the Serial Number or the "date" mask)

In short the value in AX3 is fine for the purpose of this exercise - proceed with the remainder of cells and calculations and post back if you have any problems (pref. with a sample file)

[ctrapper]

ahh.. ok.. let me finish your solution then

[ctrapper]

Ok I am getting 0's in AW3.. I am not skilled enough in EXCEL to do the MATCH and INDEX piece.. I am not sure if that's what's needed or not..

[DonkeyOte]

You would need to copy the AX formula down for all rows - this is your key for finding City2&Date in the data set.

That said, given your sample file, there are no values that will meet with your criteria - ie there is no PHI record for 29-Oct-2010 and the same holds true for every single combination in your data set.

Am I missing something ?

[ctrapper]

Maybe I did a bad job of explaining.. I need to "marry up" the cities. Row 3 contains the Value for ATL in Column AV, if you go down to Row 961, you will find PHI in Column A for that that same date, and the Value for PHI in Col AV. I want to be able to move that value up to Row 3 and put it in AW so I can do a side by side comparison... The only way I could calculate the value in AV was city by city, but I need to compare 2 at one time..

[DonkeyOte]

No, explanation was fine - I should have looked more closely at the values.

Your E values have trailing spaces meaning E <> A

AW3:
=SUMIF($AX:$AX,TRIM($E3)&"@"&$C3,$AV:$AV)
copied down

Remember though you must copy AX3 down for all rows (ie to 1251) - if you don't the above will not work.

[ctrapper]

That did it! Thanks a bunch.. wish I was better at the whole INDEX, TRIM, etc.. but I don't have that skill set! I will mark as solved!

[ctrapper]

Where do I mark solved or do you do that?

[DonkeyOte]

Quoting FAQ:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[ctrapper]

Got it! Thanks again!