Index Match Questions

[PGATC]

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

 

[jbeaucaire]

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

 

[PGATC]

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!

 

[Aladin Akyurek]

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

 

[PGATC]

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

 

[Aladin Akyurek]

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:

=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))),"")

 

[PGATC]

Awesome Thanks!

 

[Aladin Akyurek]

Awesome Thanks!

You are welcome.

 

[jbeaucaire]

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

 

[PGATC]

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!