INDEX MATCH with criteria ... and a wildcard
[removed]
Posted Dec 2, 2021, 10:11 PM UTC · edited · locked
[removed]
Posted Dec 2, 2021, 10:11 PM UTC · edited · locked
Reformatting the table so that there is one line per PoS on the lookup side is not feasible
This is entirely feasible actually, and could be done in Power Query in a minute or so. Hundreds of rows is actually pretty small compared to what it's built to handle.
Well that sounds very promising. I'll get on corporate to get me access to Power Query ..... and I'll have it installed probably by Monday :(
I've used it before and it's great, but unfortunately it's a little out of my grasp within my deadline for dealing with this. Also a great idea, BTW.
Fair enough.
How many possible PoS values could appear in a cell together? If it's something small like 2 or 3, you could just text-to-columns them, then sequentially try all the columns separately and pick the result that gave you the answer.
Solution verified!
You have awarded 1 point to stevegcook
^(I am a bot - please contact the mods with any questions. | ) ^(Keep me alive)
The maximum number is 9. And the number of rows of data I need to run this on is unfortunately 205,597 rows. So I sort of need the formula to just pick out the right thing.
Truly, this is a pain in the butt..
Oh you're saying I could do a Text To Columns (PoS 1, PoS 2, PoS 3, etc.) and then just an IFERROR until I get the right answer? That actually might work, although it would add a lot of processing time.
Yeah, it's not elegant or efficient, but it could work.
=INDEX(Rate,MATCH(1,(Code=A2)*ISNUMBER(FIND(C2,PoS&"")),0))
Solution verified!
You have awarded 1 point to finickyone
^(I am a bot - please contact the mods with any questions. | ) ^(Keep me alive)
Your SUMIFS syntax should work too, I think you’re looking at a data mismatch between Code and A2… Consider one of these.
=SUMIFS(Rates2020[Rate],Rates2020[Code],A2&"",Rates2020[PoS],"*"&C2&"*")
=SUMIFS(Rates2020[Rate],Rates2020[Code],--A2,Rates2020[PoS],"*"&C2&"*")
Yeah I think there was a formatting mismatch, maybe? Where C2=value, while [PoS]=text. Regardless, your other solution was a total winner. Thanks a ton, as always!
No worries bud
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters| |-------|---------|---| |FIND|Finds one text value within another (case-sensitive)| |IFERROR|Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula| |INDEX|Uses an index to choose a value from a reference or array| |ISNUMBER|Returns TRUE if the value is a number| |MATCH|Looks up values in a reference or array| |SUMIFS|Excel 2007+: Adds the cells in a range that meet multiple criteria|
^(Beep-boop, I am a helper bot. Please do not verify me as a solution.)
^(6 acronyms in this thread; )^(the most compressed thread commented on today)^( has 4 acronyms.)
^([Thread #10851 for this sub, first seen 2nd Dec 2021, 22:37])
^[FAQ] ^([Full list]) ^[Contact] ^([Source code])
This post has been removed due to Rule 1 - Poor Post Title.
Please post with a title that clearly describes the issue.
The title of your post should be a clear summary of your issue. It should not be your supposed solution, or just a function mention, or a vague how to. A good title is generally summed up in a sentence in your question.
Here's a long example and a short example of good posts.
Rules are enforced to promote high quality posts for the community and to ensure questions can be easily navigated and referenced for future use. See the Posting Guidelines for more details, and tips on how to make great posts.
Are your PoS codes always 2 digits? If not then e.g. 15 would get a false match on e.g. a PoS of 150.
They are always numbers and always 2 digits. There could be an issue with them formatting as numbers vs. text. But there aren't going to be issues with 150 vs 15. Great callout, though.