0

I have a list of items that have the main item name plus some others that have customization in them, like below.

enter image description here

What I'm aiming for is to find how many that are M01 - Supreme Pizza + OTHER TEXT. Not the ones with just this M01 - Supreme Pizza.

These are the 2 formulas I tried that gave me something other than an error msg. But the answer is wrong.

=MATCH(A2&"-",A2:A17,0)

=MATCH("M01 - Supreme Pizza"&"-",A2:A17,0)

enter image description here

Both these give the result 2. Which is wrong.

I've been mulling over how/if I can use query function for this, but my knowledge with that is zero.

7
  • 1
    maybe experiment with INDEX(reference, MATCH(search_key, range, search_type)) as outlined in this reference
    – 24601
    Oct 30 '21 at 10:32
  • 1
    OTOH if you were to replace the "-" after the meal code with "/" for example, you could use =COUNTIF(A1:A2,"* *") to find just those with the additional "-" text after the main descriptor so M01 - Supreme Pizza - A15P L10F would become M01/Supreme Pizza - A15P L10F as a simple fix
    – 24601
    Oct 30 '21 at 11:55
  • Are you using Excel (desktop), Excel Online, or Google Sheets?
    – Rubén
    Oct 31 '21 at 1:03
  • @Rubén Google Sheets
    – Genny
    Oct 31 '21 at 1:05
  • @ErikTyler The root of my issue is this, so I decided to post this as a separate question because I'm not going beyond this at the moment.
    – Genny
    Oct 31 '21 at 1:06
1

You can count values in A2:A that start with M01 - Supreme Pizza and continue with a space and additional characters like this:

=countif(A2:A, "M01 - Supreme Pizza *"

0

Thanks to @24601 I decided to change the format for the simple solution.

=(INDEX(A2:B17,MATCH("M01"&"-",A2:A17,)))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.