Lookup on multiple criteria to bring multiple results

Morning Guys

I have a sheet named "Tracking Log" where column F contains a numeric value and where Column C contains a category.

What i want to do is look up Column F in the "Tracking Log sheet against Cell F2 in "Sheet1" to pull all values in Column E where the category equals "Item1".

Can someone please provide me with a formula to do this?

Again the lookup data could contain multiple values so id guess it would be some kind of an index match formula which i'm not too good with.

Thanks
Dave

Hi Dave,

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

Apologies

Example attached.

I can't see an "Item 1" - do you mean "Category 1" (and it's in column D of the Tracking log sheet) ?

Pete

Apologies Pete

So yes what i want to do is ..

On the lookup tab if i type in the ticket number in Cell F2.

Then i want all the serial numbers in column E pulled through in to the relevant table on the lookup page.

You will notice that each category has 20 blank fields so each one of those should contain a unique number from the serial number column for that category and ticket number.

Some tickets and categories could only have 1 result but others could have up to 20.

Thanks
Dave

I've add you file with my solution because when I am reading my own post I feel a bit confused.
Excuse my English as it is not my native language.

Hi KOKOSEK

Your solution has multiple of the same value.

Only uniques should be pulled in

Thanks
Dave

I've changed the layout of your Lookup sheet slightly, so that each category table occupies only one column - this makes it easier to expand if you need to.

I've set up data validation in cell E2 so that you can select the Ticket number from the drop-down - this is governed by a table I have set up in column J of the Tracking Log sheet.

I've used a helper column in column H of the Tracking Log sheet, with this formula in H5:

=IF(F5=Lookup!$E$2,D5&"_"&COUNTIFS(F$5:F5,F5,D$5:D5,D5),"")

This identifies each record which matches the criteria, and gives each a unique code. Then I have used this in cell B8 of the Lookup sheet:

=IFERROR(INDEX('Tracking Log'!$E$5:$E$500,MATCH(B$7&"_"&ROWS($1:1),'Tracking Log'!$H$5:$H$500,0)),"")

This can be copied down as far as you need it, and then that block of formulae can be copied across into D8, F8 etc.

Then you can just use the drop-down to select a new Ticket number in E2, and the display will change automatically.

Hope this helps.

Pete

I did not want to recreate original layout. This solution definitely will be much, much quicker as there is no array formulas.

Originally Posted by Pete_UK

I've changed the layout of your Lookup sheet slightly, so that each category table occupies only one column - this makes it easier to expand if you need to.

I've set up data validation in cell E2 so that you can select the Ticket number from the drop-down - this is governed by a table I have set up in column J of the Tracking Log sheet.

I've used a helper column in column H of the Tracking Log sheet, with this formula in H5:

=IF(F5=Lookup!$E$2,D5&"_"&COUNTIFS(F$5:F5,F5,D$5:D5,D5),"")

This identifies each record which matches the criteria, and gives each a unique code. Then I have used this in cell B8 of the Lookup sheet:

=IFERROR(INDEX('Tracking Log'!$E$5:$E$500,MATCH(B$7&"_"&ROWS($1:1),'Tracking Log'!$H$5:$H$500,0)),"")

This can be copied down as far as you need it, and then that block of formulae can be copied across into D8, F8 etc.

Then you can just use the drop-down to select a new Ticket number in E2, and the display will change automatically.

Hope this helps.

Pete

That is perfect Pete.

Thanks for your help.

Originally Posted by KOKOSEK

I did not want to recreate original layout. This solution definitely will be much, much quicker as there is no array formulas.

That's no problem KOKOSEK, Thanks for your help either way.

Glad to help, Dave - thanks for the rep.

Us Northerners need to keep together !!

Pete

Originally Posted by Pete_UK

Glad to help, Dave - thanks for the rep.

Us Northerners need to keep together !!

Pete

No problem Pete.

ANd that's very true!