Index Match Nth Value, with wildcard

[jasonseebaluck]

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

 

[jasonseebaluck]

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]

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]

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]

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]

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]

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]

Glad to help
LOL, hero! I will think about changing jobs to Captain America