# using INDEX MATCH SMALL together

#### littlejilly

##### Board Regular
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!

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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 ?

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?

Given in "all-codes"
 code price 1 26 2 35 2 48 1 40 5 39 2 18 1 42 1 47

<!--StartFragment--> <colgroup><col width="65" span="3" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

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.
 codes prices 1 26 40 42 47 2 35 48 18 3 4 5 39 6 7 8

<!--StartFragment--> <colgroup><col width="65" span="7" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

This is gold, thank you!!!!

Replies
5
Views
124
Replies
1
Views
112
Replies
4
Views
187
Replies
11
Views
684
Replies
4
Views
193

1,219,807
Messages
6,150,348
Members
450,952
Latest member
Zung

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back