Using a function to to lookup a row # in another worksheet

Not sure how to title this, but here's what I want to do. I am looking up a value on one sheet, on a second sheet using the index and match functions (the value is to the left of the reference). The ranges are discontinuous, in that the referenced sheet has multiple tables, but the values I am looking up are in the same column. I can get it to look up consecutive rows in a table, but when I move to the next table, I have to enter the first row # by hand. I'm using the row # to make it easy on me, but I've noticed i can use any consecutive values on each table as long as the correct row # is referenced. I have many tables so I'd like to automate this to skip to the next table. I'm doing it with the row function but that can change if it makes it simpler. It's hard to explain here, but the attached workbook shows what I am trying to do.
Lookup-example.xlsx
orerockonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try this Array Formula which requires confirmation with Ctrl + Shift + Enter instead of Enter alone.

On Table Sheet,
In B2
=IFERROR(INDEX(Data!$B$2:$B$17,SMALL(IF(ISNUMBER(Data!$B$2:$B$17),ROW(Data!$B$2:$B$17)-ROW(Data!$B$2)+1),ROWS(B$2:B2))),"")

Open in new window

Confirm with Ctrl + Shift + Enter and copy it down.

Refer to the attached for details.
Lookup-example.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
orerockonAuthor Commented:
Thanks! Works great, but I am skipping big chunks of rows with values in the column, which confuses it. That's why I was looking to use the row # as a reference for the look up. It looks like what I want to do is a bit more complicated than this and I'm in a crunch for time, so hand editing the reference ranges is what I'll do for this job. I'll keep it for reference though, it will come in handy with other workbooks :)
orerockonAuthor Commented:
For got to ask, is there a way to just put text into a reference? For example, a formula references cell A100, and there is a row function that puts row 100 into another cell. So if I have a formula that references cell A100, use the result of the row function to generate the value. Or, a concatenated column and row function that generates the cell reference, not just the row. For example something like VLOOKUP(MID(CELL("address",A1),2,1)&row(A1),2,0) (which doesn't work as far as I can tell).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.