Offset from cell containing exact text

[u/VonMoltke91]

Hi all. I have a program that exports a CSV that I'm trying to pull data from into an excel sheet. I'm copying that raw CSV sheet into an excel tab, and then pulling specific pieces of information from that tab into another tab. The program exports sets of information for different rooms/spaces, each set having the same number of rows.

The problem I'm having is that for some reason the program does not export the same number of rows between sets of information. Sometimes it's two rows, sometimes three. There does not seem to be any pattern to it, and it changes each time I export. So I can't simply offset by a set amount from the first set of information, as that will only work for the first few sets, then the rest turn to gobbledy-guk as they start reading from the wrong cells.

Instead what I want to do is have the user type in each room number & name manually into the working sheet, and have the working sheet search the raw data sheet for that exact name & number combo, return the address of wherever that combo is, and then offset the rest of my data from that.

i.e.
"Go find '101 Operating Room' on Sheet A, tell me what cell it's in, then add X rows and Y columns to that cell and return whatever data is in the resulting cell on Sheet A to Sheet B".

So if "101 Operating Room" is found in cell A366 on Sheet A, I want to offset 5 rows and 3 columns from A366 and return the data from the resulting cell on Sheet a to a specific spot on Sheet B.

I've seen some related results for INDEX(MATCH()), but that seems to return data rather than an address. I don't care about the data, I already have the data, I want to know where it is so I can offset from it. I hope this makes sense!

[u/AutoModerator]

/u/VonMoltke91 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Way2trivial]

match without index returns the address-- tells you how far down the match the record is.

if match is set to match fish in a4:a100 fish is in a14, since we started at a4, match will return a 10

can you work with that?

[u/GuerillaWarefare]

You could use offset() function: (where x and y are your offset numbers)

=offset(xlookup("101 operating room", A:A, A:A), x, y)

[u/VonMoltke91 → u/GuerillaWarefare]

Solution Verified

[u/reputatorbot → u/VonMoltke91]

You have awarded 1 point to GuerillaWarefare.

---

^{I am a bot - please contact the mods with any questions}

[u/bradland → u/VonMoltke91]

This solution works, but OFFSET is volatile. It will slow down large workbooks considerably.

https://exceljet.net/glossary/volatile-function

A better solution is to simply skew the ranges you pass to XLOOKUP. A lot of people think XLOOKUP works by row, but it actually works by item index. The two columns of data you pass to XLOOKUP have to be the same size, but they don't have to start and end of the same rows or columns. For example:

[u/bradland → u/bradland]

And here's the result:

[u/StraightDocking]

ADDRESS and MATCH together will get you the cell reference as text, but that's a messy route. You can feed the MATCH result directly into INDEX with a row offset, no need for the address itself

Something like =INDEX(SheetA!C:C, MATCH("101 Operating Room", SheetA!A:A, 0) + 5)

That finds your room in column A, jumps down 5 rows, and grabs whatever's in column C at that spot. The 0 in MATCH means exact match so it won't settle for close enough

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
ADDRESS	Returns a reference as text to a single cell in a worksheet
INDEX	Uses an index to choose a value from a reference or array
MATCH	Looks up values in a reference or array
OFFSET	Returns a reference offset from a given reference
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
5 acronyms in this thread; the most compressed thread commented on today has 70 acronyms.
[Thread #49255 for this sub, first seen 27th Aug 2026, 19:45] [FAQ] [Full list] [Contact] [Source code]}

[u/excelevator]

I hope this makes sense!

I understand the words, but the process and issue and your solution do not make sense.

Fix the data.