MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Index/Match


Posted by Scott on May 18, 2001 8:01 PM

I'm wondering if index/match is the right choice or not. I'm wanting to return a value from a list, but there are identical lookup values and index/match only returns the first value it finds. I want to return the second value instead of the first, but can't figure out how to do this.....Example
a1= hammers b1=360 c1=$10
a2= saws b2=200 c2=$5
a3= hammers b3=20 c3=$15
a4= hammers b4=275 c4=$10
index(a1:c4,match(hammers,a1:a4,0)*match($10,c1:c4,0),2)

this will return b1=360 instead of b4=275....Can someone help me with this? Thanks


Posted by Kevin James on May 18, 2001 9:31 PM

Hi Scott,

I really hope someone proves me wrong, but I've been monitoring this board for a while now and I've seen this question come up in various scenarios. It comes down to find the nth iteration of a duplicate.

Most functions in Excel are designed to find the first instance, all others being considered invalid duplicates.

In your case, I would strongly recommend redesigning the database. You need to key on a unique identifier. earlier this week, another poster wrote about a similiar problem--two vendors using the same part number for totally different items. The logical design in that case would have been to key on Vendor AND ProductCode. In your case, the design is confusing. though I appreciate that it is possible to have 3 brands of hammers or 3 quality grades of hammer or whatever, designing the data on this non-unique description is not helping you.

As I mentioned, I have been monitoring this board for while and have yet to see this resolved.

Sorry,
Kevin

Posted by Kevin James on May 18, 2001 10:12 PM

Solution, but caution

Scott,

I designed a spreadsheet to illustrate how to find the nth instance of record just because of the recurring requests. While it works, it also illustrates the inherent dangers of poor database design. Look at both tabs.

You can download it at:
(broken link)

The file is: InstanceFinder.xls

Kevin

Posted by CM on May 18, 2001 11:09 PM

Re: Solution, but caution

But look what happens if you change A1:A4(on your sheet "Sorted") to Alan instead of Bart

Posted by CM on May 18, 2001 11:48 PM

Re: Solution, but caution


Here's an alternative way :-

Using the sample data in your workbook, enter in C1 and fill down to C7 :-
=IF(A1=$G$14,B1,"")

Select cells D1:D7 and array enter (Ctrl+Shift+Enter) :-
=IF(ISERR(SMALL(IF($C$1:$C$10<>"",ROW(INDIRECT("1:"&ROWS($C$1:$C$10)))),ROW(INDIRECT("1:"&ROWS($C$1:$C$10))))),"",INDEX($C$1:$C$10,SMALL(IF($C$1:$C$10<>"",ROW(INDIRECT("1:"&ROWS($C$1:$C$10)))),ROW(INDIRECT("1:"&ROWS($C$1:$C$10))))))

Column C can be kept hidden.


Posted by CM on May 18, 2001 11:58 PM

Easier way


Use autofilter to filter by "Bart" and then select Column B visible cells only and copy/paste to another column.
Also, a macro could be written to do this.


Posted by Dave Hawley on May 19, 2001 10:14 AM

Scott, how about a custom function.

Hi Scott

As you have been shown you can use a mega array for this, but try this Custom function.

Function FindNth(rTable As Range, vVal1 As Variant, _
vVal2 As Variant, iv2LookinCol As Integer, _
RetrnCol As Integer, iOccurence As Integer)

'Written by OzGrid Business Applications
'www.ozgrid.com

Dim i As Integer
Dim iCount As Integer
Dim rCol As Range

For i = 1 To rTable.Rows.Count

If rTable.Cells(i, 1) = vVal1 And _
rTable.Cells(i, iv2LookinCol) = vVal2 Then
iCount = iCount + 1
End If

If iCount = iOccurence Then
FindNth = rTable.Cells(i, RetrnCol)
Exit For
End If

Next i

End Function

...To use it, push Alt+F11 then go to Insert>Module and paste it in. Push Alt+Q to return to Excel and save.


Now put this in any cell:
=FindNth(A1:C4,"hammers",10,3,2,2)

Where A1:C4 is the entire table.

"hammers" is the value to find in column 1 of A1:C4

10 is the second value to find.

3 is the Column in A1:C4 to find 10

2 is the Column in A1:C4 to return the result from.

2 is the occurence number of "hammers".

You can see this better if you push Shift+F3 and scroll down to "User defined" and then select "FindNth".


If you run into any problems let me know.


Dave


OzGrid Business Applications

Posted by Kevin James on May 19, 2001 3:10 PM

CM, hi

My goodness, I hope you didn't get a brain cramp over that.

I realized that the suggestion had inherent flaws. But given Scott's real situation, lets hope he's not changing "hammer" to "M.C. Hammer"

I'm impressed that you worked so hard for a solution. What really needs to be done here is for the user to redesign their database. One problem I've had with many messages left here is that we (solution posters) keep trying to make sense out of bad data. Personally, I wasn't willing to work that hard.

Take care