vlookup for 2 values

[tata_ancona]

Morning all,

I am not sure how to explain what I need but I will try!

I got a spreadsheet from 2007 until 2016 with flow data. I would like to excel populate G2, H2, I2... if column A and B match with F2 and G1 (f2 and H1, f2 and I1...). I am using the following formula but it only works for the date.

=IFERROR(VLOOKUP(F2,$A$2:$C$167883,3,0),IFERROR(VLOOKUP(G1,$A$2:$C$167883,3,0),0))

so

Column A is date
Column B is time
Column C is volume
Column D and E is empty
Column F i date
Column G1, h1 .... is


Please see below how my spreadsheet looks like.

A B C D E F G H I J K....

[TABLE="width: 1386"]
<tbody>[TR]
[TD]1 Date[/TD]
[TD]Time[/TD]
[TD]Volume[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]00:00[/TD]
[TD]00:15[/TD]
[TD]00:30[/TD]
[TD]00:45[/TD]
[TD]01:00[/TD]
[TD]01:15[/TD]
[TD]01:30[/TD]
[TD]01:45[/TD]
[TD]02:00[/TD]
[TD]02:15[/TD]
[TD]02:30[/TD]
[TD]02:45[/TD]
[TD]03:00[/TD]
[TD]03:15[/TD]
[/TR]
[TR]
[TD]2 03/07/2007[/TD]
[TD]10:45[/TD]
[TD="align: center"]9.49[/TD]
[TD][/TD]
[TD][/TD]
[TD]03/07/2007[/TD]
[TD]9.49[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]3 03/07/2007[/TD]
[TD]11:00[/TD]
[TD="align: center"]10.72[/TD]
[TD][/TD]
[TD][/TD]
[TD]04/07/2007[/TD]
[TD]10.55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4 03/07/2007[/TD]
[TD]11:15[/TD]
[TD="align: center"]10.7[/TD]
[TD][/TD]
[TD][/TD]
[TD]05/07/2007[/TD]
[TD]10.72[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5 03/07/2007[/TD]
[TD]11:30[/TD]
[TD="align: center"]0.04[/TD]
[TD][/TD]
[TD][/TD]
[TD]06/07/2007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6 03/07/2007[/TD]
[TD]11:45[/TD]
[TD="align: center"]6.99[/TD]
[TD][/TD]
[TD][/TD]
[TD]07/07/2007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7 03/07/2007[/TD]
[TD]12:00[/TD]
[TD="align: center"]0.02[/TD]
[TD][/TD]
[TD][/TD]
[TD]08/07/2007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8 03/07/2007[/TD]
[TD]12:15[/TD]
[TD="align: center"]8.69[/TD]
[TD][/TD]
[TD][/TD]
[TD]09/07/2007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9 03/07/2007[/TD]
[TD]12:30[/TD]
[TD="align: center"]7.93[/TD]
[TD][/TD]
[TD][/TD]
[TD]10/07/2007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10 03/07/2007[/TD]
[TD]13:15[/TD]
[TD="align: center"]8.12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

hope it make sense and thanks in advance for your help!

Regards,

 

[Special-K99]

May be better to post a spreadsheet

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

Attachments

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[inactiveuserps07]

@tata_ancona, it looks like you're just trying to change the layout of the data in your original lists to a vertical layout. You could do this with a Pivot Table. Or you can try this:

1. In G2:

=INDEX($C$2:$C$350000,SUMPRODUCT(--($A$2:$A$350000=$F2)*($B$2:$B$350000=G$1)*(ROW($A$2:$A$350000)-1)))

2. Drag copy across as far as needed.

3. Select G2 through the last column you copied to in that row, and drag-copy down as far as needed.

If you've got information for every quarter hour for nearly 10 years, you're going to wind up with over 300,000 rows; that's why the range in the formula goes to 350,000.

That's a lot of calculation for Excel to do, so it may take a while. I would would recommend, when everything is done calculating, that you select the entire range from G2 to the bottom righthand corner of your new data set, use Ctrl-C to copy it to the clipboard, then hit Ctrl-Atl-V to Paste Special. Click the "Values" radio button and "OK." This will past the actual numbers into the grid, which will open a lot faster than trying to run the calculations every time the sheet is opened.

 

[inactiveuserps07]

@tata_ancona, hold off on that formula until I can put some error handling in for no-match situations.

 

[inactiveuserps07]

OK, here's the updated formula:

=IFERROR(INDEX($C$2:$C$350000,IF(SUMPRODUCT(--($A$2:$A$350000=$F2)*(ROUND($B$2:$B$350000,5)=ROUND(G$1,5)))=0,"",SUMPRODUCT(--($A$2:$A$350000=$F2)*(ROUND($B$2:$B$350000,5)=ROUND(G$1,5))*(ROW($A$2:$A$350000)-1)))),"")

Again, for that much data, it's going to take a while to process all of it.

I think you should first look into just using a Pivot Table to show the data the way you'd like it. Much easier and quicker.

 

[inactiveuserps07]

Shortened version of the formula:

=IFERROR(INDEX($C$2:$C$350000,SUMPRODUCT(--($A$2:$A$350000=$F2)*(ROUND($B$2:$B$350000,5)=ROUND(G$1,5))*(ROW($A$2:$A$350000)))-1),"")

 

[tata_ancona]

Hi ErikTyler,

Thank you very much for your help exactly what i needed.

Regards

 

[inactiveuserps07]

@tata_ancona, great ... and thanks for confirming that it works.

By the way, the only reason I included the ROUND() functions is that I don't know if you are manually entering the times (00:00 - 23:45), or if you are incrimenting them by formula, such as =G1+TIME(0,15,0). So I planned for both. If you are manually entering the times, you don't need the ROUND() functions.

Essentially, Excel calculates times a tiny bit differently — to 7 or 8 decimal places before it's different — if you enter a time as opposed to using a time function; and this would cause other functions that compare entered times to formulaic times to say they are different (even if only by ten-millionths) when they are, in fact, supposed to be the same.