# Index Match Questions

#### PGATC

##### New Member
Whenever I use Index and Match to find a value it only gives me the first response, but I need it to give me a response given a very specific criteria.
 A B 1 Lookup Return 2 100 Broccoli 3 4 Number Food 5 100 Broccoli 6 215 Orange 7 100 Apple Pie 8 456 Beef Stew 9 200 Pizza 10 319 Burger

<colgroup><col><col></colgroup><tbody>
</tbody>
 B2=INDEX(B5:B100,MATCH(A2,A5:A10,0))

<colgroup><col><col></colgroup><tbody>
</tbody>

My problem is I don't want it to return Broccoli, I need it to return any value that has the word "Pie". I know I need a * for the wildcard but I am not sure how else to format it. I am assuming some sort of If statement.

Thanks

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
this works for me in B2:

=INDEX(\$B\$5:\$B\$10, MATCH(A2&"*Pie", INDEX(\$A\$5:\$A\$10&\$B\$5:\$B\$10, 0), 0))

I found a work around, but I am sure I'll have to do that again and I'll give that formula a try. Thanks!

What is the look up value that you want to look up -- 100 or Pie?

I was trying to lookup 100 and return any values with the word Pie in it.

I was trying to lookup 100 and return any values with the word Pie in it.

 Number Food 100 100 Broccoli pie 215 Orange Return 100 Apple Pie Apple Pie 456 Beef Stew Pie 200 Pizza 319 Burger 100 Pie

<TBODY>
</TBODY>

D4, control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````
=IFERROR(INDEX(\$B\$2:\$B\$8,SMALL(IF(\$A\$2:\$A\$8=\$D\$1,
IF(ISNUMBER(SEARCH(D\$2,\$B\$2:\$B\$8)),ROW(\$B\$2:\$B\$8)-ROW(\$B\$2)+1)),
ROWS(D\$4:D4))),"")
``````

Awesome Thanks!

The formula in post #2 worked for me based on your post #1 sample, did it not work for you?

I found a way to solve my problem by creating a new column so I haven't tried either of these formulas. But the next time I have to do this i'll be sure to give both of these a try. Thanks again!

Replies
2
Views
172
Replies
16
Views
3K
Replies
4
Views
424
Replies
3
Views
197
Replies
8
Views
439

1,220,011
Messages
6,151,449
Members
451,028
Latest member
greekness1

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