Match, Offset, Match, Index, Countif = VLOOKUP Handles Duplicates

1. Match, Offset, Match, Index, Countif = VLOOKUP Handles Duplicates

Hi all,

I have an issue with returning values from duplicates.

A: 10000 Names (They include both Characters and Numbers)
B: 1000 Prices (Only Numbers - Numerical Values, Non-Negative)

C: List of Prices
D: Return the Names

I want to search for a name by PRICE. Because the prices many times are repeated the vlookup stops at the first name it reads.

I have read somewhere on internet that with MATCH, OFFSET, MATCH, INDEX, COUNTIF I can create an array formula.
MATCH -> Finds the row which first value occurs
Offset -> It is used for the next match (N+1)
2nd MATCH --> which will offset the function and skips the row which contains the first match and will return a row number relative to the top of its array.
Index --> to read the table of data
Countif --> How many MATCH (down-counter) to count. When is 0, it is supposed that we have found all the values we requested at the beginning.

A sample formula which I started is this one =INDEX(\$A\$5:\$A\$60,MATCH(C1,\$A\$5:\$A\$60,0)) but I dont know how to combine the things above to make it work. I have been looking around the forum and although I thought that I could find something, it ended up without success.

Any help would be much appreciated. Thank you

2. Re: Match, Offset, Match, Index, Countif = VLOOKUP Handles Duplicates

Perhaps post a small example of your sheet showing "before" and "after"?

3. Re: Match, Offset, Match, Index, Countif = VLOOKUP Handles Duplicates

Hi, please find the sample file here. Thank you very much for any help.

This is the formula I managed to find but it doesnt work with the duplicates - it returns error when it finds the duplicate.

=INDEX(AF:AF,SMALL(IF(AJ\$5:AJ\$60=AN26,ROW(AJ\$5:AJ\$60)),COUNTIF(AN\$20:AN26,AN26)),1)

Any help much appreciated

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1