Vlookup or equivalent returning 10 matches across 2 data ranges.

[mudstuffin]

I have two data ranges that I need to look for up to 10 instances of the same match and return the values in the next columns. I can get the Vlookup to check through both ranges to find the first match, but I can’t get it to return the next instances of the next match. I have two data ranges as my data has gone over the 65k rows. I could have maybe 6 rows with the same reference number, and I want to return up to ten matches.

Here is a typical set-up of the data…

data1
ref name date value
123456 bob 11/05/04 100
123456 bob 13/05/04 180
234567 james 16/05/04 250
456789 mike 19/05/04 50

data2
ref name date value
123456 bob 03/06/04 95
234567 james 06/06/04 125
123456 bob 07/06/04 85


I have used the below formula to look up the first match.

=IF(NOT(ISNA(VLOOKUP(B4,Data1,2,FALSE))),VLOOKUP(B4,Data1,2,FALSE),IF(ISNA(VLOOKUP(B4,data2,2,FALSE)),"No Match",VLOOKUP(B4,data2,2,FALSE)))

This finds the match and returns the name. I then have some more of these in the same row returning the different column lookups for date & value. What I need to do is the same for the other matches.

So when I am on the lookup sheet, I want to be able to type in 123456 in the search box, and it return 4 matches that look through both data ranges.

Ref name date value
123456 bob 11/05/04 100
123456 bob 13/05/04 180
123456 bob 03/06/04 95
123456 bob 07/06/04 85

The data is pulled from an external database and needs to be sorted by date, which means that the same reference can be in both ranges. Can anyone help on this one…..?

I hope it makes sense !

Thanks in advance.

mudstuffin

 

[ETID]

Have you tried the Auto Filter, under the Data menu?

 

[xlbo]

Over 65000 rows.....may I humbly suggest you ditch excel and port over to Access

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[ETID]

...or filter the data before you bring it in to excel

...or better yet, make a live link to your external data from within excel...from the menu Data>Import External Data>New Database Query, follow the wizard or (use MSQuery when prompted, it is gui similar to access, but less robust,..here is where you would declare paramters or prompt to user for criteria to pass to a parameter)

...this way you can query on the fly and return only the records that you need.

 

[mudstuffin]

Thanks for the replies.

I'm afraid Its gotta be in Excel, and I need to return all of the matches as I need to be able to track the history of those matches. My lookup sheet handled the 65k rows ok as I am only doing 40 lookups.

The data is sourced from an external access database using the MSQuery, but as mentioned above, I need to have all of the matches come up so filtering would not be appropriate.

Any ideas on the lookup or equivalent formula needed...?

Thanks again.

mudstuffin.

 

[xlbo]

Can't do it in a standard formula - you would need either a macro or a UDF - utilising the FIND / FINDNEXT functionality

You would create a function in a module - lets say it is called "myLookup" which would have an argument to pass to it to say which match you want to find so in a cell, it would look like
=myLookup(A1,DataRng,3)

where 3 would be the 3rd instance of the match, A1 the lookup key and DataRng, the range containing the data



Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Zathras]

This may or may not be what you are looking for, but take a look at my VLOOKUPNEXT function in Thread68-693835 or search for VLOOKUPNEXT to see other variations on the theme.

 

[SkipVought]

Hi,

If it's gotta be in Excel, I would use Data/Get External Data to set up a query of both tables of data. It would be a snap! ;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884