Match blank cell

[tiredofit]

In column A, I have 5 rows, a,b,c,d,e. In cell C1 have the value c.

In cells D1, I have this formula:

=Match(C1,A1:A5,0)

which returns 3 as expected.

If instead column A contained a, "",c,d,e and cell C1 contains a blank cell, the match function returns N/A.

How can I change to return the value of 2?

Thanks

 

[lrobbo314]

Book1
	A	B	C	D
1	a			2
2
3	c
4	d
5	e
Sheet2

Cell Formulas
Range	Formula
D1	D1	=IF(ISBLANK(C1),AGGREGATE(15,6,ROW(A1:A5)/(A1:A5=""),1),MATCH(C1,A1:A5,0))

 

[tiredofit]

Book1
	A	B	C	D
1	a			2
2
3	c
4	d
5	e
Sheet2

Cell Formulas
Range	Formula
D1	D1	=IF(ISBLANK(C1),AGGREGATE(15,6,ROW(A1:A5)/(A1:A5=""),1),MATCH(C1,A1:A5,0))

Thanks

 

[Chowmann78]

Hi,
try this in D1

=IFERROR(MATCH(C1,A1:A5,0),2)

Hopefully this works for you.

 

[tiredofit]

Hi,
try this in D1

=IFERROR(MATCH(C1,A1:A5,0),2)

Hopefully this works for you.

No because it's not always going to be 2!