Use INDEX, MATCH without hard coded cell address

[GScott]

I would like to build a formula that is user proof.

I have tapped this out;

=INDEX(BusTable,MATCH(Q5,BusNo,0),2) and it works fine. This is in cell R5.

In an attempt to replace all direct cell references I have written this;

[TABLE="width: 334"]
<tbody>[TR]
[TD]=INDEX(BusTable,MATCH((ADDRESS(ROW(),COLUMN()-1))),BusNo,0),2)[/TD]
[/TR]
</tbody>[/TABLE]

but is returning a #N/A because of the address part. I have the wrong syntax.

How do I fix this?

Thanks for any help

 

[Aladin Akyurek]

I fail to see how ADDRESS makes this formula user proof, except that it will intimidate some users.

You have a paren too many, try:

=INDEX(BusTable,MATCH(ADDRESS(ROW(),COLUMN()-1),BusNo,0),2)

 

[GScott]

I fail to see how ADDRESS makes this formula user proof, except that it will intimidate some users.

You have a paren too many, try:

=INDEX(BusTable,MATCH(ADDRESS(ROW(),COLUMN()-1),BusNo,0),2)

Well it means it can be pasted anywhere in the column and still return the correct entry.

Unfortunately this is not working - after removing the ). The error is #N/A.

The ADDRESS(ROW(),COLUMN()-1) is returning a text field or at least something written in parenthesis. Could this be the problem?

 

[Aladin Akyurek]

Well it means it can be pasted anywhere in the column and still return the correct entry.

Unfortunately this is not working - after removing the ). The error is #N/A.

The ADDRESS(ROW(),COLUMN()-1) is returning a text field or at least something written in parenthesis. Could this be the problem?

What is the value of Q5?

 

[GScott]

it is a number - 112

And the address part returns "$Q$5" including the rabbit ears

 

[Aladin Akyurek]

it is a number - 112

And the address part returns "$Q$5" including the rabbit ears

Try:

=INDEX(BusTable,MATCH(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),BusNo,0),2)

 

[GScott]

That did it.
Thanks very much

 

[Aladin Akyurek]

That did it.
Thanks very much

You are welcome.