trying use index match to pull information from database to individual sheet

[garvey1973]

I have a data base of information. I want to pull out all the information from that database into a new sheet based off a single criteria. So in attached sheet, I have list of trading cards, on sheet 2 if i put a name in the yellow box, all of that persons cards will be listed only..... This is driving me nuts

[DBY]

Hello
Take a look at the attached reply. It uses an helper column to bring back the relevant rows of data. This could be replaced with an array formula if needed. Also a pivot table might be a non-formula option.

Hope this helps.
DBY

[José Augusto]

Hi
This is my resolution with a extra column

Select Sheet2!L5:L45 and put the array formula

=IFERROR(SMALL(IFERROR((1/--('Inventory of Cards'!$F$6:$F$180=Sheet2!A2)*ROW(A6:A180)),""),ROW(A1:A180)),"")

Select Sheet2!A5:K5 and put the array formula

=IFERROR(OFFSET('Inventory of Cards'!$A$1:$K$1,Sheet2!L5-1,0),"")

Copy A5:K5 down

See the file Card Inventory Help.xlsm

[garvey1973]

Hello
Take a look at the attached reply. It uses an helper column to bring back the relevant rows of data. This could be replaced with an array formula if needed. Also a pivot table might be a non-formula option.

Hope this helps.
DBY

It does help - however I was trying to do it with an array, If i have 100 or 200 different names it may be easier -- can you help with that? THANK YOU so much

[DBY]

The helper column applies to the rows in the 'Inventory of Cards' sheet. An array is less efficient and the number of names will make no difference as you're only selecting one at a time. The validation list for the name lookup however, is going to be a long one. Is the Inventory of Cards table fixed or will it be expanding? If so it would help to convert it into an Excel table which is dynamic.

[garvey1973]

It will be expanding -- I have no idea how to convert to Excel table --- Im trying to teach myself as I go along and need things

[DBY]

Here's a Table version with the helper column. The formulas are added as you enter new data. The column can be hidden if needed. There's also a Pivot example.

[garvey1973]

I really like they both work... thank you for your help...... now i need to learn how you did that