# lookup multiple records with one criteria

• I have a somewhat long list of names in different columns with all of their info. What I need to do is find a way to lookup rows of info if I input a first name, or last name, or company name, etc. The problem I am having is that there is more than one person in the list with the first name john or the last name smith and other such names. I need to find a way to look up ALL records that contain the first name john and be able to pick which one is the right one rather than just finding the first one in the list.

The easiest way to deal with this would be combine first name with last name and lookup accordingly but the list was not put together well so some people don't have first names or last names or either (some just have company names).

Sorry for the long message but I guess my question is if you have a nice way of finding every record that matches a certain criteria rather than just the first one in the list. I have a much simpler worksheet that I made up that simulates the idea if you think that would help at all.

## Files

• The formula I used is formula array, you need to get out of the formular bar by holding down Ctrl + Shift keys and press Enter after you paste the following:

Formula in cell E2

=IF(COUNTIF(\$A\$1:\$A\$6,\$E\$1)>=ROW(A1),INDEX(\$A\$1:\$C\$6,SMALL(IF(\$A\$1:\$A\$6=\$E\$1,ROW(\$A\$1:\$A\$6),""),ROW(A1)),COLUMN(A1)),"")

Good luck!

Jindon

## Files

• thank you sooo much...i have been trying to think of something like that forever now!

• Hello everyone.

I've been away from the forum for quite some time, but I hope to be back for good soon, to continue my Excel education.

I'm replying to this thread to ask if there are any other methods using formulae to achieve this kind of result? My brain's a little rusty at the moment.

The scenario that I have is this . . .

I have a database containing client details. Each new transaction is recorded on a new row in the database with the client name being in column A. There may be more than one entry for a client in column A but with different details in columns B through to O.
What I'd like to do is to have a separate sheet in the workbook that acts as a display page, whereby when a client name is entered into a cell (e.g. A1) a list of all transactions for that client appears underneath.
I don't particularly want to use Excel's filtering functions or VBA. I've achieved the desired effect using the suggested formulae in this thread (many thanks to the author), but as this solution uses array formulae it slows down the processing of the workbook.

So, in a nutshell . . . is there a way to achieve my goal without the aid of filtering functions, VBA, array formulae, or double-sided sticky tape?

Paul.

PS. It's good to 'see' you all again.

Paul.

• Welcome back.

The above code should do what you want. You'll either need to call it from another routine and pass the record id to search for. Or you'll need to rewrite it so that it gets the data internally. This code written right here so it hasn't been tested.

And once again weclome back.

Regards,
Barry

My Favorite New Thing:
Dynamic Named Ranges

If you get a response that answers your query or you see a response that explains something for you. Maybe think about "LIKING" that response let the member know he/she has helped another.

• Ok so I see that you didn't want a VBA approach. May I as why not. It will typically run better than a formula base approach.

However, if you want a formula based approach follow this algorithm.

1. Database HAS to be sorted.
2. Do a countif function to determine how many entries that the entered
record ID has.
3. Using the Match function determine the offset to the first entry for that
particular record ID.
4. Then using the INDEX function to retrieve the data from the specified
record and field.

Why this approach. The display page will need to have many rows of formulas ( need to account for a record ID which shows up alot ) then each field or column needs to have it's own formula. Then everytime the page changes or even the workbook changes ( and you haven't turned of the manual calculation ) all of these formulas will need to be recalculated. Then what if the size of your data range changes or goes outside the bounds of your formulas.

The VBA approach is a much better solution. You can add a button and assign the code and the display list is just a click away.

Regards,

Regards,
Barry

My Favorite New Thing:
Dynamic Named Ranges

If you get a response that answers your query or you see a response that explains something for you. Maybe think about "LIKING" that response let the member know he/she has helped another.

• Hi Paul,

The following is a formula-based approach, without the use of array formulas.

Sheet 2:

A1: enter the client of interest

B1: enter a 0 (zero)

B2, copied down:

=IF(Sheet1!A2=Sheet2!\$A\$1,LOOKUP(9.99999999999999E+307,Sheet2!\$B\$1:B1)+1,"")

C1:

=LOOKUP(9.99999999999999E+307,B:B)

D2, copied down:

=IF(ROW()-ROW(\$D\$2)+1<=\$C\$1,MATCH(ROW()-ROW(\$D\$2)+1,B:B,0),"")

E2, copied across and down:

=IF(N(\$D2),INDEX(Sheet1!B:B,Sheet2!\$D2),"")

Hope this helps!

... So, in a nutshell . . . is there a way to achieve my goal without the aid of filtering functions, VBA, array formulae, or double-sided sticky tape?

Well if you don't want to use a function and you don't want to use a VBA macro and a computer doesn't have any use for sticky tape. I guess your stuck to powering down the pc, breaking out the abacus, and dig through those paper files. Ooops that's right we're trying to save the envrionment and we inputed those files into the system.

Just a little humor,

:cheers:

Regards,
Barry

My Favorite New Thing:
Dynamic Named Ranges

If you get a response that answers your query or you see a response that explains something for you. Maybe think about "LIKING" that response let the member know he/she has helped another.

• Folks,

The response times on here never cease to amaze me! AWESOME!

Thanks for the responses so far.

iwrk4dedpr - Thanks for your responses and advice. I'm reluctant to use VBA because I really know very little about it. It's on my to-do list but there always seems to be something else to do. If I knew more about it and how to modify the codes I would probably use it, but at this time I don't think that VBA is the best option for me. Your help (& humour) is appreciated though.

Domenic - Thanks for your prompt reply too. It appears to be a formula based solution that doesn't involve arrays. I haven't had chance to try applying it to my particular scenario yet, but I do hope to a little later this evening. Would I be asking too much for you to explain exactly how your solution works? I tend to learn more from laymans terms than from just looking at formulae and trying to figure them out. I understand if you don't have time.

Thanks again folks.

Paul.

Paul.

• Paul,

Basically, the formula in B2 looks at the first row of data (Row 2) on Sheet 1 to see if the value in Column A matches the criteria specified in A1 of Sheet 2. If it does, it flags it with the number one. It then does the same thing for the next row. If it matches, it flags it with the next number, number two, and so on.

The formula in D2 gives us the position within Column B of the first record that matched the criteria. Then for the next row, it tells us the position within Column B of the second record that matched the criteria, and so on.

The formula in E2 returns a value from Column B on Sheet 1 referenced by the position named in D2. Then for E3, it returns a value from Column B on Sheet 1 referenced by the position named in D3, and so on.

Unfortunately, for a better understanding, you'll have to refer to the Help menus and study the spreadsheet. At first glance, it may be intimidating. But as you begin to study it, you'll find that it's not very complicated at all. Also, this approach can be adapted to suit a variety of situations.

• Got it working!

Domenic,

You're absolutely right. I've spent the last hour or so modifying your solution to fit my needs and, while I don't fully understand it all yet, it is already much clearer. Apologies for requesting the 'in-depth' explanation, but thanks very much for providing it all the same.

I've got your solution working a treat - NICE ONE!

Thanks very much,

Paul.

Paul.

I've got your solution working a treat - NICE ONE!

Quote

Thanks very much,

Paul.

You're very welcome!

Cheers!

• Argument Not Optional

This will shock most of you but I've just been experimenting with the possibility of using a VBA solution for this. I tried using the code that iwrk4dedpr provided in this thread (thanks Barry) but I keep getting the error message 'argument not optional' when I attempt to run the code in my application.

I know less than a little about VBA so would someone be good enough to explain this error and why I'm getting it?

Many thanks,

Paul.

Paul.

• I'm just guessing here but did you pass the strRecordID to the code Barry wrote.... it needs to know what to look for...

here's the originals code

[vba]Sub RetrieveAllRecords(strRecordID As String)

' Local Variables
Dim rngData As range
Dim rngDataOut As range

' Step 1 : Loop through all records and extract desired records
For Each rngData In worksheets("DataBase").Range("A:A")
If rngData = "" Then exit For
If rngData = strRecordID Then
rngDataOut.entirerow = rngData.Entirerow.Value
Set rngDataOut = rngDataOut.offset(1,0)
End If
Next rngData

End Sub [/vba]

You'd need to pass the srtRecordID to the macro in some way.... this would be an example of how

[vba]Sub GetRecords()
Dim strRecord As String
strRecord = Application.InputBox("Enter the Record ID", Type:=2)
Call RetrieveAllRecords(strRecord)
End Sub[/vba]

You could also have this code embedded in Barry's macro... I left it as it was though in order to illustrate what you were doing wrong & how you would utilise such code....

Hope this helps

• My hat's off to you Moody. Using VBA will greatly expand the possiblities that XL has to offer.

If you still need help on how to integrate the code I had written let me know, and thanks Will for the assist. I should have coded that other part as well.

Regards,
Barry

Regards,
Barry

My Favorite New Thing:
Dynamic Named Ranges

If you get a response that answers your query or you see a response that explains something for you. Maybe think about "LIKING" that response let the member know he/she has helped another.

• No worries....

I'd like to see a Coding Toad hanging around so I thought I'd help out

• Oh deary deary me . . . I think I might get to like this VBA business!

I have it working . . . very primitive at the moment . . . but very smooth and very nice! DAMN! Need to rethink everything now.

Need lots more help though . . .

Whenever I click the button it asks me to 'enter the record ID', but I'd like it to automatically read from one cell, e.g. A1. I'd also like to have all previous records cleared when the button is clicked for a new search. These requests are probably very easy but could someone point me in the right direction?

Many many thanks to both Will and Barry for their VBA help thus far - nice one fellas.

Paul.

Paul.

• Yep. I'd like to see a coding Toad as well. Only as long as he's happy and hops to it.....

:cheers:

Regards,
Barry

My Favorite New Thing:
Dynamic Named Ranges

If you get a response that answers your query or you see a response that explains something for you. Maybe think about "LIKING" that response let the member know he/she has helped another.

• Paul

If the lookup data will always be in cell A1 then use my previous code. I'll repost it with the changes to get the data from a static location.

I've made the incomming argument optional. This way you can either call the routine from another routine and pass the value or you can leave the passed value blank and it will read from the Range("A1"). You'll need to change the sheet name to the appropriate value.

Keep Hopping

Regards,
Barry

My Favorite New Thing:
Dynamic Named Ranges

If you get a response that answers your query or you see a response that explains something for you. Maybe think about "LIKING" that response let the member know he/she has helped another.

• Ok, bit by bit I'm getting this working. I do, however, suspect that this VBA lark might not be quite as easy as you magicians make it look!

So . . . Instead of finding the reference cell in the database and returning the entire row of data associated with it, how can I get it to return only certain columns? e.g. I want it to ignore the first 6 columns on the specified row and return everything after that.

Thanks,

Paul.

Paul.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!