# Index match query

#### Fiachracallanan

##### New Member
• I am looking to return a sequential list of items which match to an element of text that I have selected. An example will explain what I want better.

• Item Selected Tom

1. Ref Amount
2. Tom C 32206 3683
3. Tom B 36212 34464
4. Frank 10552 43642
5. Mary 13228 30534
6. Tom A 30694 35798
7. Mary B 9121 27489

• So I want a Formula which will return the Reference for items in column A which contain the word Tom.
• So the Result I want is:
• Selected

• Tom C 32206 3683
• Tom B 36212 34464
• Tom A 30694 35798

• The formula I have to return an item containing Tom is:
• =INDEX(\$A\$5:\$A\$10,MATCH("*"&\$B\$2&"*",\$A\$5:\$A\$10,0))
• But this only returns the first Tom and no other.
• The Formula I have to return sequential items with exact reference:
• =INDEX(\$B\$4:\$B\$11, SMALL(INDEX((\$B\$2=\$A\$4:\$A\$11)*(MATCH(ROW(\$A\$4:\$A\$11), ROW(\$A\$4:\$A\$11)))+(\$B\$2<>\$A\$4:\$A\$11)*1048577, 0, 0), ROW(\$A1)))
• This will (if I change the row reference at the end) return all Tom C's (If I change B2 to "Tom C") in sequential order but only the Tom C's and not the other Tom's (Tom B and Tom A). I have tried replacing the \$B\$2 with "*"&\$B\$2&"*" but that doesn't seem to work. I would really Appreciate help on this.
• If you want me to clarify further please post here and I will try my best.

Last edited:

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hello,
This formula returns any occurrence of tom, the only downside is that if you are looking for "tom" and "tomcat" or "atom" is in the index, they will be returned.
index a5-a10
search item in b2
enter the formula in c8 (it is a Ctrl Shift Enter) and copy it down six rows.
=IF(\$B\$2="","",IFERROR(INDEX(\$A\$5:\$A\$10,MATCH(SMALL(IFERROR(SEARCH(\$B\$2,\$A\$5:\$A\$10)+ROW(\$A\$5:\$A\$10)/1000,""),ROWS(\$C\$8:C8)),IFERROR(SEARCH(\$B\$2,\$A\$5:\$A\$10)+ROW(\$A\$5:\$A\$10)/1000,""),0)),""))

Hope it helps,
(borrowed the formula from Matt Paul https://www.youtube.com/watch?v=IimQLOkZ61Q&list=PL18CB8BD71E932861 )
He has a great series on text manipulation
marcj

Hi
Welcome to the board

Although you can get each value with a single formula it's much more efficient and simple if you use an auxiliary column to get the row of the Tom that you want to display

For the auxiliary column, in I2:

=IFERROR(SMALL(IF(ISNUMBER(SEARCH("Tom",\$A\$2:\$A\$7)),ROW(\$A\$2:\$A\$7)),ROWS(\$E\$2:E2)),"")

This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER.

Copy down

To get the result values, in E4:

=IF(\$I2="","",INDEX(A:A,I2))

Copy down and across.

Hello,
This formula returns any occurrence of tom, the only downside is that if you are looking for "tom" and "tomcat" or "atom" is in the index, they will be returned.
index a5-a10
search item in b2
enter the formula in c8 (it is a Ctrl Shift Enter) and copy it down six rows.
=IF(\$B\$2="","",IFERROR(INDEX(\$A\$5:\$A\$10,MATCH(SMALL(IFERROR(SEARCH(\$B\$2,\$A\$5:\$A\$10)+ROW(\$A\$5:\$A\$10)/1000,""),ROWS(\$C\$8:C8)),IFERROR(SEARCH(\$B\$2,\$A\$5:\$A\$10)+ROW(\$A\$5:\$A\$10)/1000,""),0)),""))

Hope it helps,
(borrowed the formula from Matt Paul https://www.youtube.com/watch?v=IimQLOkZ61Q&list=PL18CB8BD71E932861 )
He has a great series on text manipulation
marcj

Hey thanks that worked perfectly.

You're welcome. Thanks for the feedback.

Replies
3
Views
559
Replies
5
Views
623
Replies
24
Views
819
Replies
4
Views
692
Replies
4
Views
376

1,217,386
Messages
6,136,283
Members
450,002
Latest member
bybynhoc

### 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