Lookup_array problem, when using MATCH function

[CameRian]

Hello,

I'm having a #VALUE error when using a MATCH function.
Basically I want to introduce the lookup_array automatically depending on the position of another cell. I want to change the range of the match automatically, starting from the same row of a specific row.

The problem is when I substitute the lookup_array of for exemple B2:B5 with a function that results in exactly the same text (B2:B5), occurs the #VALUE error. For example, I'm inserting: "B"&VALUE(ROW(B1)+1)&":B5" instead of B2:B5.

I tried to change the VLOOKUP table_array in the same way, and it also gave me that error.

Any help is appreciated.

[Jonmo1]

Try

=MATCH(val,INDEX(B:B,ROW(B1)+1):$B$5,0)

[CameRian]

Thank you very much! Using the INDEX next to : solved the problem

[Jonmo1]

You're welcome.