Formula to Pull Multiple Results from Column Matching Same Criteria

Hello All,

I have an Excel file that contains Data about shipments imported from SAP via a report pulled. The data can constantly change for every column/ row any second of the day. This report is run each day, dumped into excel on a sheet, and then pulled via Index Match formula to the main sheet/template.

My problem that no one can seem to find a solution to, is that the lookup value (shipment #) can have multiple different materials shipping on that same shipment (with the same lookup value) and in the same column (same lookup array). The lookup will only pull the first material number it sees in the column. I have no other unique identifiers to pull each material number using a multiple lookup.

My question is, Is there any way to create a unique identifier between different sheets? I'm currently trying to look up the shipment number on the main sheet and the material number on the second sheet where it is correct (imported from the report). Here is the current formula I'm trying to run and the result is N/A

=INDEX{(LOR!G:G,MATCH(Data!G2&LOR!G:G,Data!G:G&LOR!G:G,0))}

(I've seen some methods where Excel will show you each row that the material is in using an (IF,SMALL,ROW) function but that is time consuming and would not work in my situation. I'm looking for the least manual method possible.)

This is my first post so please let me know if you need more info, etc. Thank you and sorry for the length of the post.

Welcome to the forum!

Your thread title is really not good. I will amend it for you this time, as you are a new member, but in future you must ensure that your titles explain what you are trying to do.

Please attach a workbook.

What happens with Match and Vlookup is that once they find what they are looking for, they stop looking. Only the first value that matches is returned.

There are two ways to handle this. One way is to make a composite key of what makes a record unique. In this case, it appears to be the SO#, DEL#, Shipment and Material. Then you can do the lookup on the composite. I put a "helper column" in the Data From Report to show you what that might look like. You would have to make an identical composite key on the sheet where you are doing the lookup.

I did not go through with this. Instead, I used a pivot table.

First I converted the data from report to an Excel Table. Excel tables have a number of benefits: they "know" how big they are so pivot tables, formulas and charts that reference them do not have to be changed when rows are added or deleted. Also they "remember" formulas and copy them down automatically, so if you have formulas in helper columns, you don't have to worry about them.

So in the example shown here, you could delete the table rows (not the header) and copy in the next day's data. The helper column formula is filled in automatically for all rows.

On the pivot table sheet, all you have to do is right click in it and select refresh.

The two selection panels are called slicers. It is worth looking them up. They are nice-looking filters but are more powerful than that. Best of all, they are very easy to implement.

Here is a quick blurb on tables: http://www.utteraccess.com/wiki/Tables_in_Excel

I do not have a good reference for pivot tables, I learned them so long ago that even if the reference still existed it would probably be obsolete.

P.S, you do not need the helper column if you use the pivot table. I only included it to show an alternative method of lookup. Some times pivot tables worn't work for what you want when you want to do a lookup with multiple criteria. Composite keys can be the answer.

Yes, that is exactly what I did. I usually stick some kind of delimiter between he pieces (in this case a colon) which makes it a bit easier for people to read.