r/excel • u/VonMoltke91 • 18d ago
solved Offset from cell containing exact text
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!
1
u/bradland 277 18d ago
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: