Lookup based on data in 2 columns

[seanomuiri]

Hi

I want to come up with a lookup formula to find data based on looking at data from 2 columns.

I've attached an example file of what I want to do - the columns highlighted in yellow are the columns to lookup - the 'Concatenated' column is exact match, and the 'Time' column is within 5 minutes (i.e. the Time on main sheet is original time entered in one database, the Time on lookup data sheet is time entered in another database shortly after, so Time on lookup data sheet will always be slightly later but within 5 minutes of time on first sheet).

Is there a way to match both these columns to the lookup sheet so as to bring back the data in the Data columns?

Regards
John

[JohnTopley]

Add "helper" column in J of "Lookup Data"

=D2&E2

Copy down


in F2 of "Main Sheet"


=IFERROR(INDEX('Lookup Data'!F$2:F$22,MATCH($D2&$E2,'Lookup Data'!$J$2:$J$22,1)+1),"")

copy across and down

[seanomuiri]

hi - thanks, but I dont think that's entirely accurate - the "Helper" column wont have exact times so I dont know how that will work accurately? I dont see where in the formula that it takes into account the small difference in time of up to 5 minutes. How is it matching accurately?

And I can see that it's not working for all the data - for example, row 5 on Main sheet is not pulling back data?

[JohnTopley]

Try

in F2 ("Lookup Data"

=INDEX('Lookup Data'!F$2:F$21,MATCH(1,('Lookup Data'!$D$2:$D$21='Main Sheet'!$D2)*('Lookup Data'!$E$2:$E$21>='Main Sheet'!$E2-TIME(0,5,0))*('Lookup Data'!$E$2:$E$21<='Main Sheet'!$E2+TIME(0,5,0)),0))

Enter with Ctrl+Shift+Enter


Copy across and down

[bebo021999]

Non-array formula in F2:
I do not use the concatenate column.

Please Login or Register  to view this content.