Using MATCH function without blank cells

Question

My excel sheet is as below

enter image description here

When I use the following formula the result is 8

=MATCH(5;B8:AS8;0)

I would like to expand this formula; not adding blank cells to range (B8:AS8).

I mean if I don't add blank cells to range result will be 3

Which function must I use to expand my formula?

score 5 · Accepted Answer · answered Aug 20 '13 at 16:57

5

Give this a try:

=COUNT(B8:INDEX(B8:AS8,MATCH(5,B8:AS8,0)))

answered Aug 20 '13 at 16:57

tigeravatar

26,199
5
30
38

score 2 · Answer 2 · answered Aug 20 '13 at 16:23

2

Try this (basically, you're subtracting the blank cells):

=MATCH(5,B8:AS8,0)-COUNTIF(B8:AS8,"")

answered Aug 20 '13 at 16:23

PowerUser

11,583
20
64
98

1

Alternately, instead of Countif, you could use Countblank – tigeravatar Aug 20 '13 at 16:26
thank you but this is not correct algoritm. When i try use your suggestion with "5", result is 3. Yes this is correct but if i change "5" to "1" or "9" result is not correct. – Kerberos Aug 20 '13 at 16:34
I took another look and I now see what you mean. Countif() or even Countblank() won't work for this problem. I'll have to discount my own answer then. Glad you found @tigeravatar's answer useful. – PowerUser Aug 20 '13 at 18:23

score 0 · Answer 3 · answered Aug 20 '13 at 16:48

The following uses MATCH to find the location of the sought-after element, and COUNTBLANK to remove all blanks between the start of the list and the location of the found element:

enter image description here

In your specific case, you would probably use

=MATCH(5,$B$8:$AS$8,0)-
 COUNTBLANK(INDIRECT("R8C2:R8C"&MATCH(5,$B$8:$AS$8,0),FALSE))

If the sought-after does not exist in the list, #N/A is returned.

Using MATCH function without blank cells

3 Answers3