Index Match Nth Value, with wildcard

jasonseebaluck

New Member
Joined
Jul 6, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. MacOS
Hello All,

I am a little new to this excel forum thing, am self taught, so apologies in advance. I have had a cursory look at some similar forum queries and am unable to work backwards to fix my problem.

My original problem: find the 2nd shift code in D132 along a list E$82:E$124 and reference shift worker on that shift in column ($L$82:$L$124)
-D132 = shift code (which was simply the letter and number "D2")

fixed with the following:
=IFERROR(INDEX($L$82:$L$124,SMALL(IF($D$132=E$82:E$124,ROW(E$82:E$124)-ROW(E$82)+1),2)),"Vacant")

My new problem:
-My manager now wants the second shift of either D2 or D2X codes to be matched.
-So I initially tried to keep the same formula above. However in the shift code reference, I placed "D**" in D132, to pick up either D2 or D2x...however it did not work.
-I have read on the forums that I could use IF(ISNUMBER(Search)...but unsure whether this will provide the function I require?

Any help would be gratefully received.

Jason
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

jasonseebaluck

New Member
Joined
Jul 6, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. MacOS
Attempted the following ISNUMBER(SEARCH)

=IFERROR(INDEX($L$82:$L$124,SMALL(IF(ISNUMBER(SEARCH($D$132&"*"&"|"&$D$131, E$82:E$124&"|"&E$82:E$124)),ROW(E$82:E$124)-ROW(E$82)+1),2)),”Vacant”)

But get the error "?#Name"
 

jasonseebaluck

New Member
Joined
Jul 6, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. MacOS
Ok managed to fix the problem with the following:

=IFERROR(INDEX($L$82:$L$124,SMALL(IF(ISNUMBER(SEARCH($D$132, E$82:E$124)),ROW(E$82:E$124)-ROW(E$82)+1),2)),”Vacant”)

However when attempting to locate the potential third shift worker
via: =IFERROR(INDEX($L$82:$L$124,SMALL(IF(ISNUMBER(SEARCH($D$132, E$82:E$124)),ROW(E$82:E$124)-ROW(E$82)+1),3)),”Vacant”)
I get "?Name", when a 3rd worker is not working that shift

very confusing. Thank you all in advance for listening to my ramble
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
check
=IFERROR(INDEX($L$82:$L$124,SMALL(IF(ISNUMBER(SEARCH($D$132, E$82:E$124)),ROW(E$82:E$124)-ROW(E$82)+1),3)),"Vacant")
 

jasonseebaluck

New Member
Joined
Jul 6, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. MacOS

ADVERTISEMENT

Hey Sandy,

=IFERROR(INDEX($L$82:$L$124,SMALL(IF(ISNUMBER(SEARCH($D$132, E$82:E$124)),ROW(E$82:E$124)-ROW(E$82)+1),3)),"Vacant")

Thanks for your reply, so the formula works whenever there is a shift worker populated. However whenever there is not a worker on the day, I get "?name" rather than "vacant".

Also to clarify since completing this formula, I have populated $D$132 with "D2*", rather than D2, to pick up both D2 or D2X shifts.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
You are welcome
I resolved your problem with #NAME?
look at quotation marks:
my
,"Vacant")
yours
,”Vacant”)
for the rest you need to solve it yourself or wait for someone else :)
 

jasonseebaluck

New Member
Joined
Jul 6, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. MacOS
You are welcome
I resolved your problem with #NAME?
look at quotation marks:
my
,"Vacant")
yours
,”Vacant”)
for the rest you need to solve it yourself or wait for someone else :)
Sandy you are a hero, all fixed! Apologies for the lack of understanding.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
Glad to help
LOL, hero! I will think about changing jobs to Captain America :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,251
Messages
5,600,540
Members
414,386
Latest member
PARAMATHMA SENTHILNATHAN

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