Finding the three lowest scores

[spydey]

So I have, what I think, is a somewhat complicated scenario that I need to solve. I have bits and pieces but can't seem to get it to all come together in to the end result I am seeking.

I have a single workbook.
Two worksheets.

Sheet 1:
ItemNames in column A and Locations in column B.
There are 80+ unique ItemNames, and 53 unique Locations.
The ItemNames starts in A3 and the Locations start in B3
Row 2 contains the column headers.

Sheet 2:
ItemNames in column A and Locations in column B.
There are 80+ unique ItemNames, and 53 unique Locations.
They are not all in the same order as Sheet 1.
The ItemNames starts in A3 and the Locations start in B3
Row 2 contains the column headers.
Columns I through Q contains numerical data for each combination of ItemName and Location.

I hope that helps to some degree.

I need to do the following:

Give me the headers of the columns that contain the 3 lowest values for each ItemName & Location combination.

Example: In sheet 1, cell D3:F3, I need the names of the columns from Sheet 2 that contain the lowest values for the same ItemName & Location combo as found in Sheet 1 Cells A3 & B3.

I have used the following, in sheet 1 cell D3, before to get the headers of the columns that contain the lowest value:

=INDEX(Sheet2!$I$2:$Q$2,0,MATCH(SMALL(Sheet2!$I3:$Q3,COLUMNS($D$3:D3)),Sheet2!$I3:$Q3,0))

I then fill this over through F3.

But I have two issues with using this:

1. It doesn't allow me to get the data for the same ItemNames & Location combo as in Sheet1. The ItemNames & Locations from sheet1 may not be in the same order as sheet2. I was thinking of using an Index(Match, as I have for other things, but am not sure how to incorporate it to use the above formula.

2. If any of the 3 lowest values are the same, then I get the same header, the first one in the series, for any of the same values, even though the headers are all different. Example: values of 0, 2, 0. My headers would return as Header1, Header2, & Header1; when it should be Header1, Header2, Header3.

Hopefully that all makes sense!! I am a little confused myself, so I hope I didn't pass that on in my writeup! hahahaha

Thanks as always.

-Spydey

So in essence I guess it would be a formula whose output would be this:

Give me the unique column headers of the 3 lowest values from Sheet2 where ItemName & Location match the ItemName & Location from Sheet 1. The results would be placed in Sheet 1.

 

[Eric W]

The pipe is used to make sure we don't get false matches, like:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ann[/TD]
[TD]eliason[/TD]
[/TR]
[TR]
[TD]anne[/TD]
[TD]liason[/TD]
[/TR]
</tbody>[/TABLE]


Rare, true, but it happens. Depends on the data too. If you combine numbers with text, it's not really necessary, but it's a good habit to get into.

And yes, your proposed alternate snippet should work the same. I haven't tested it, but I'd guess they both are roughly equal in efficiency.

 

[spydey]

Perfect. Thanks for the quick and easy to follow explanation.

Take care!

-Spydey