Apache OpenOffice Community Forum

Hi All,

I am working on a formula that will be utilizing the MATCH function, which it's my understanding takes a single column lookup array. I know that I could simply make a named range covering a single column for the array I am looking to use with this function, but as I already have a multi-column named range that includes the range/column that I am looking to use, I'm wondering if there a way to reference that single column within the larger array.

Example: I have a named range 'Persons' covering cells A3:D40. The information I want to utilize is located in column B. Is there a way to reference only this column while calling the 'Persons' range in my formula?

I was thinking perhaps integrating the INDEX function or something similar, but have been unable to get it to work, so I'm unsure if I am simply doing it wrong or if it's just not possible.

Thanks in advance.
A.Fish

Tips:

• Just name the range B3:B40 as "PersonsB". Then use the name "PersonsB" in your cell functions.

• Use some lookup function to ge the values from the second column of the "Persons" cellrarange.

A.Fish wrote: ↑Thu Jan 09, 2025 6:26 pm … formula that will be utilizing the MATCH function … perhaps integrating the INDEX function …

=MATCH(value;INDEX(range;0;2);0) looks for a match of the value in the second column of the range.

If you need any additional assistance attach a spreadsheet demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). I will not help further unless you attach a spreadsheet document. Read about INDEX and MATCH in Help → Index or in User Guides (PDF) or searching for topics about them in the Calc Forum.

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

Awesome! Thank you MrProgrammer, this is exactly what I was looking for! I am able to get it to work now the way I was hoping with the final formula being : =INDEX(range;MATCH(value;INDEX(range;0;2);0);1)

Thanks again for the assistance.
A.Fish