I need to search 10,000 rows of data to find the first row where two of the cells in that row match two values in my input data and the time-values in two more cells in that row bracket a third time-value of my input data. I want to take the first positive match, return a fifth output value in the associated row and then repeat this process for the remaining 4,000 lines of input data (so it needs to be automated).
Is VBA the way to go to do this multi-parameter lookup or is there a way to get this done with only Excel functions?
When data is stored in multiple columns, but you have some sort of consistent and ongoing need to compare/match multiple values, it's simplest of all to create a helper column that puts these values together into a single searchable column.
Let's say the values in columns A, B and C need to be matched as a group. In an empty column, put this helper formula, then copy down:
=A2 & "-" & B2 & "-" & C2
Now, you can put your search values together the same way and do an exact match search. If that helper column were in G, and the value you wanted back was column D, on sheet2 you could use cells A1, A2 and A3 to indicate your search values, then this formula in A4:
=INDEX(Sheet1!$D:$D, MATCH(A1 & "-" & A2 & "-" & A3, Sheet1!$G:$G, 0))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thanks, Jerry. This is helpful. The only aspect that I still need to sort out is that the time input will be bracketed by the data that I'm searching, rather than a direct match (i.e. does the input time fall between the starting time and the ending time specified in the given row). Any more ideas?
Not blind ideas, no. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook and we can look at this directly together.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks