Index Match Questions

PGATC

New Member
Joined
Sep 12, 2014
Messages
5
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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
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

New Member
Joined
Sep 12, 2014
Messages
5
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!
 

PGATC

New Member
Joined
Sep 12, 2014
Messages
5

ADVERTISEMENT

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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))),"")
 

PGATC

New Member
Joined
Sep 12, 2014
Messages
5

ADVERTISEMENT

Awesome Thanks!
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
The formula in post #2 worked for me based on your post #1 sample, did it not work for you?
 

PGATC

New Member
Joined
Sep 12, 2014
Messages
5
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,961
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top