Index Match not knowing the range? 2

[Kenja824]

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

http://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.

 

[structSU10]

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.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Well I think this is a solution.

[tt]
=OFFSET($A$1,MATCH(A12,OFFSET($A$1,1,MATCH("SPOT #",[highlight #8AE234]$1:$1[/highlight],0)-1,[highlight #FCE94F]6[/highlight],1),0),MATCH("X_POS",[highlight #8AE234]$1:$1[/highlight],0)-1,1,1)
[/tt]

This assumes that
1) your headings are in Row [highlight #8AE234]1[/highlight]
2) there are [highlight #FCE94F]6[/highlight] rows of data.

Returning your workbook with the formula in the appropriate rows.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

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

http://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.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[3DDave]

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.

 

[Kenja824]

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

http://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.

 

[SkipVought]

"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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]