How to write an excel index match formula with varying criteria?

[Kelmike]

Good day,

I have this workbook with 3 sheets (Data, 1st and 2nd). In Data, i have a table with index match formulas to auto populate the values
of each HBins (1~12). So far it was working for most of the time but then I found out that some search pattern I'm using were changing,
hence, instead of returning the correct value, it just shows 0 since my search string does not match anymore. Is there a way I can make my
formula adapt to this varying search patterns? Im thinking something like an OR function. Would that be possible?

Here are samples of the different search strings for HBIN 1. This is almost the same for the other HBINs, the number just change:

Enter Good Bin Qty
Good Bin 1
Hardbin 1
Hardbin1
HBin1

My current pattern will just match "Enter BIN 1 Quantity" and ENTER BIN 1 QTY", but not "Enter BIN1 Quantity".

Appreciate the help you may extend, thanks a lot in advance.

 

[arthurbr]

Perhaps like Enter*BIN*1Q*

 

[Kelmike]

Perhaps like Enter*BIN*1Q*

Hi arthurbr, I tried and it didn't work. So far I found no search string with 1 and "Q" together. Thanks for the time checking this though,appreciate it.

 

[jasonb75]

Assuming no duplicate bins, try

=SUM(SUMIFS('2nd'!$D:$D,'2nd'!$B:$B,{"BIN 1 Q*","BIN1 Q*"}))

 

[arthurbr]

Hi arthurbr, I tried and it didn't work. So far I found no search string with 1 and "Q" together. Thanks for the time checking this though,appreciate it.

Must have missed one
Enter*BIN*1*Q*

 

[jasonb75]

Enter*BIN*1*Q*

That wouldn't be able to differentiate between Bins 1, 10 to 19, 21, 31, etc

I don't think that any formula is going to catch all variations without becoming far more complicated than should be necessary, it would be better to use more consistent methods of data entry rather than use messy formulas.

 

[Kelmike]

That wouldn't be able to differentiate between Bins 1, 10 to 19, 21, 31, etc

I don't think that any formula is going to catch all variations without becoming far more complicated than should be necessary, it would be better to use more consistent methods of data entry rather than use messy formulas.

Yeah I agree on you with that, but I guess that's not under control anymore . Thanks anyway for the time checking on this, appreciate it.

 

[Kelmike]

That wouldn't be able to differentiate between Bins 1, 10 to 19, 21, 31, etc

I don't think that any formula is going to catch all variations without becoming far more complicated than should be necessary, it would be better to use more consistent methods of data entry rather than use messy formulas.

Assuming no duplicate bins, try

=SUM(SUMIFS('2nd'!$D:$D,'2nd'!$B:$B,{"BIN 1 Q*","BIN1 Q*"}))

hi jasonb75, that formula is returning 0 when I tried, thanks for checking on this though, appreciate it

 

[Kelmike]

hi jasonb75, that formula is returning 0 when I tried, thanks for checking on this though, appreciate it

Im using office 2010

 

[Kelmike]

hi jasonb75, that formula is returning 0 when I tried, thanks for checking on this though, appreciate it

Hi jasonb75, i checked again your formula and adjust the search criteria based on the actual string and it worked! that's really awesome. Could you please explain how it's working? I never though the combination of sum and sumif will do the trick