Using MATCH function without blank cells

3
votes

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?

3
excelexcel-formulaexcel-match

3 Answers

5
votes

Give this a try:

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

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

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

0
votes

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.