using INDEX MATCH SMALL together

[littlejilly]

Hello all,

I have a workbook which contains two different worksheets. The first sheet ("Unique Codes") has several different lists. In Column B I have each unique code.

In the second sheet ("All codes"), each code is listed again in Column B with an associated price in Column C. Each code could show multiple times and the number for each code varies (anyway from 1-5x).

Going back to my Unique Codes sheet, I need to be able to pull in the associated prices for each code. I previously used a vlookup but my issue here is that it stops once the code is found the first time in All Codes. Someone has previously suggested to me using index, match, and small together to help pull in all assicated prices with a particular code.

Any response would be great, I am not sure how to string the calculation of these three functions together to get to the answer I require. Ideally, it would be great if in Column D of Unique Codes, I can have something that would read all associated prices with the code given in Column B, such as, $1.43, $1.55,...,etc.

Thanks!

 

[vds1]

Just to confirm

sheet : Unique codes || COL B- Unique code || COL D - Price Values
sheet : All codes || COL B- Unique code || COL C - Price Values needed via lookups ??

In All codes C2 try,
=VLOOKUP(B2,'Unique Codes'!B:D,3,0)

What made you think you need small function ?

 

[littlejilly]

Hi,

Someone has previously suggested it. I am not familiar with index,match, or small functions or the use of them as an alternative to vba.

The issue with the vlookup is that it will only list the price found associated with the first found unique code.

For example, on all codes, the code 76125-0784-25 is listed in rows 83 and 112. With a vlookup, it will only provide me with the price associated with 76125-0784-25 found in row 83.

MIght you have a recommendation to return all prices associated with the code?

 

[cyrilbrd]

Given in "all-codes"
[TABLE="width: 195"]
<!--StartFragment--> <colgroup><col width="65" span="3" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 65"][/TD]
[TD="width: 65"]code[/TD]
[TD="width: 65"]price[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]47[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

In "Uniques-codes" formula in D2 is =IFERROR(INDEX('all-codes'!$C$2:$C$9,SMALL(IF('all-codes'!$B$2:$B$9=$B2,ROW('all-codes'!$B$2:$B$9)-ROW(B$2)+1),COLUMNS($D$2:D2))),"") Ctrl + Shift + Enter not just enter on a PC or Command + Return on a mac.
Copied down and right till needed.
[TABLE="width: 455"]
<!--StartFragment--> <colgroup><col width="65" span="7" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 65"][/TD]
[TD="width: 65"]codes[/TD]
[TD="width: 65"][/TD]
[TD="width: 65"]prices[/TD]
[TD="width: 65"][/TD]
[TD="width: 65"][/TD]
[TD="width: 65"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

 

[littlejilly]

This is gold, thank you!!!!

 

[cyrilbrd]

This is gold, thank you!!!!

Most welcome.