I want to reference entire row from another as argument in my function, normally I would use something like this: OtherSheet!27:27

But what, if 27 is a dynamic value stored in other cell?

For example, I have MATCH function looking like this:


and value 27 stored in cell A1. When I'll change the value to 28 I want my MATCH function look like this:


What should I use to achieve that?


What you are looking for is Excel’s INDIRECT function which allows to reference ranges specified as literal values, be they stored elsewhere, passed literally or a combination of both. Thus,

=MATCH(B6; INDIRECT("OtherSheet!" & A1 & ":" A1); 0)

will evaluate to

=MATCH(B6; OtherSheet!27:27; 0)

if cell A1 contains the value 27.


=MATCH(B6;OtherSheet!27:27;0) Is a search function which should return the value that is in cell 27, which may not be 27.

Change (B6;OtherSheet!27:27;0) to (B6;OtherSheet!A1:A1), to return the value stored in cell A1.

  • I'm sorry if I didn't explain it clear enough - English isn't my native language, but that's not what I'm trying to achieve. I want my MATCH function to search row, which number is stored in cell A1. So when value stored in A1 is 27 - search OtherSheet, row 27, when value in A1 is 35 - search OtherSheet, row 35 etc. – Piotr May 14 '17 at 19:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.