Ask Your Question

How can I define area for MATCH or VLOOKUP function?

asked 2016-10-04 15:27:09 +0200

Lars J gravatar image

In Calc, I am attempting to make a function that will search within an area that is defined by another cell. More precisely, I want to be be able to enter the name of the sheet. As an example, suppose I want to find the row in area Sheet2.A1:A50 where the cell has the value "apple". I could do this with the function =MATCH(Sheet2.A1:A50;"apple";0) which would return the row number for "apple" if it exists, and return an area if it does not. However, I would like to apply this to a series of sheets with different names, so I would like to get the sheet name ("Sheet2" in the example) from a different cell. I have tried things like =MATCH(B1&".A1:A50";"apple";0) to get the sheet name from the contents of cell B1. But I only get an error message.

Is a thing like this possible, and how?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2016-10-04 15:38:30 +0200

Lupp gravatar image

updated 2016-10-04 15:43:25 +0200

0: The row can only be found if "apple" does not occur a second time in the search range.
__Otherwise you get the first occurrence top down (or left to right if applicable).
1: The value to search for must go to the first parameter position of MATCH.
2: To convert an address string into a reference use INDIRECT.
3: Often the OFFSET function offers more flexible ways to get range references.

=MATCH("apple" ; INDIRECT(B1 & ".A1:A50") ; 0)
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-10-04 15:27:09 +0200

Seen: 92 times

Last updated: Oct 04 '16