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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

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,210
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,017
Messages
5,834,959
Members
430,330
Latest member
drAli77

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top