×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Index Match not knowing the range?
2

Index Match not knowing the range?

Index Match not knowing the range?

(OP)
I have a sheet with maybe up to 30 columns and can be up to a couple thousand rows.

I need to find the column with the header SPOT#, look up a specific number (ie... 23x456) in that column, then return the information in the same row in column with the header X_POS

What causes me the problem is that I cannot be certain which column will have the different headers. This is a file someone gives to us and I copy and paste to an excel tab. Every time we get it, for some reason the columns are in a different order.

A very simplified version is attached. Thanks

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Index Match not knowing the range?

I would likely try to use a combination of XLOOKUP and INDIRECT so I can let it search the headers for that specific name, return the column number it is, and use the indirect to build the reference.

RE: Index Match not knowing the range?

Ken,

Can you verify that...
"SPOT #" could be in any column and
"X_POS" could be in any column
...so that both headings need to be searched for.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Index Match not knowing the range?

2
Well I think this is a solution.


=OFFSET($A$1,MATCH(A12,OFFSET($A$1,1,MATCH("SPOT #",$1:$1,0)-1,6,1),0),MATCH("X_POS",$1:$1,0)-1,1,1)


This assumes that
1) your headings are in Row 1
2) there are 6 rows of data.



Returning your workbook with the formula in the appropriate rows.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Index Match not knowing the range?

(OP)
Hey Skip

Thats right. When we get the file created from another group, for some reason the columns are not always in the same order. I dont understand why, because they have a program that creates the file. But each person who runs it seems to get something different depending on how they do it.

I have an off the wall question on this stuff when it comes to VBA. Because this file I am creating has a lot of formulas and they are copied and pasted down thousands of rows. This file is getting kind of large for an excel formula. I find that a couple times today it locked up and I had to restart it. ..... I know if I was to use VBA, all the formulas would be sort of built into the file instead of filling each cell with them. This would make it a much smaller file.

M question is, would the majority of the work to change this over to VBA be in gathering all the different formulas, or would there be just as much work in translating the formulas into VBA coding?

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Index Match not knowing the range?

Ken,

First, check out my solution above.

Then to your "off the wall" question...

I can't make a definitive judgement. It all depends on the nature of the beast.

I can say that if you're getting external data with columns periodically, in a different order each time, I'd be arranging the data in a standard format and incorporating the data into a single source as a preliminary step.

I haven't had a chance to look at the file you sent several weeks ago. My wife's been in recovery from an incident 2 months ago and we have been visiting doctors and therapists of various stripes. This problem was a tasty morsel and interesting departure from the present circumstance, that was quick to solve.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Index Match not knowing the range?

Skip - yours is a method I used successfully before; it was a sheet where various contributors would add their own columns, plus it generalizes nicely by placing the searched column names at the top of the target column rather than in the formula.


As to the other question - VBA has a different syntax for referring to cells than the cell formulas. so there is some effort getting used to it. There are tens of thousands of example on the internet, so copy/paste is a good way to use something that is close. It also requires the use of loops to operate over the subject ranges. Start simple and be prepared to be perplexed by some VBA design choices.

RE: Index Match not knowing the range?

(OP)
Thanks Skip

That worked perfect once I realized I had to make the range larger to fit my file. lol

This one isnt for the same exact file as before, but I may use it for that one. :)


I will be praying for your wife, that it is God's will she gets better.

Ken
www.OneGodLogic.com
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

RE: Index Match not knowing the range?

"to make the range larger to fit my file"

I should have included a tip for this. You can use COUNTA() on a column that has a value for every row of data, to return the row count to a function like OFFSET().

Ken, thanks for your earnest prayer.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close