Index and Match Pricing Dilemma

It is always challenging when you have data set up the way you have it, with a product name in 1 cell, followed by a series of empty cells - before the next product is listed. Although it IS possible to work with a layout like this, it generally requires unnecessarily convoluted formulas.

My approach is (almost always) to use a helper column to fill in those blanks. This makes for much cleaner, simpler formulas.

Assuming you would normally have row 1 containing headers, I have inserted a row above your data, so PRODUCT A is now in A1. For this exercise Col C is my helper
Then...
C2=IF(A2="",C1,A2)
copied down as needed.

Do the same for G2, copied down
G2=IF(E2="",G1,E2)

These helpers could really be anywhere.

Now you have a continuous list of products

To your question
in H2, I used...
H2=COUNTIFS($B$2:$B$61,F2,$C$2:$C$61,G2)
copied down.

This shows 0 throughout, so no matches were found.

Is this something you could work with?

Post deleted

Post deleted.

OK, this could probably be done with INDEX/MATCH, what are you comparing against what, and where would you want the answers - and how shown?

1. I dont see that you need the gaps btw your STD prod prices - nor the 2nd col of names (Q), so I removed them.

2. How about if, in your STD list (Q now that the 2nd list of names are no longer there), you have something like this, which will tell you how many times you exceeded your STD price?
=COUNTIFS(C:C,O3,B:B,">"&P3)

We could then use Conditional Formatting to ID those products you paid more for?
Or, even run a calc to show how much more you ended up paying more than STD?
=SUMIFS(B:B,C:C,O3,B:B,">"&P3)

So your table in O:R would look like this...

	O	P	Q	R
2	Itime - Individual	Std Price	QTY > STD	SUM > STD
3	PRODUCT A	$ 779.00	4	3808
4	PRODUCT B	$ 383.00	3	2503
5	PRODUCT C	$ 873.00	4	4232
6	PRODUCT C	$ 267.00	13	9942
7	PRODUCT D	$ 225.00	29	21728