Dumb Excel Questions

The Sandbox Software/Technology
1

I am probably overthinking this. I am not great with this kind of thing.

Let’s say I have a range of data in Excel, columns A through D. I want to use index match to look up based on a key identifying field. Pretty straightforward.

Column A - Key
Columns B, C, D - Data
X = Key lookup
Y = Column name of data being looked up

I would use =index(A:D, match(X,A:A,0), match(Y,$1:$1,0)) in that case

So what if the column with the Key info is not fixed? How could I use index match to look it up then, so it works no matter what order columns A through D are in? I feel like I would need to imbed another index match somewhere, but my brain is getting knotted trying to think through this. I’ll probably feel foolish when the answer is obvious.

2

When possible, I try to have only one key, perhaps creating a new key column in which I do some math for the express purpose of having only one key.

There have been times where I’ve used OFFSET in selecting one of several possible keys
(e.g. INDEX(range,MATCH(lookupvalue,OFFSET()))…but that wouldn’t be my go-to tactic in a file I’m expecting to share with others, or which I expect to be routinely updated.

3

Yes, you can embed an extra index match. But it’s super ugly.
Index(A:D, MATCH(X,index(A:D,0,match(key, $1:$1,0)),match(Y,$1:$1,0))

So I don’t know . Maybe your tons of if statements ARE better.

I’d generally make a new better Key, or return multiple answers.

2 Likes
4

Sounds like you’ve got the variable name already

3 Likes
5

How un-fixed is it? I mean, are you getting different data sets where the goofy intern is messing up the order of columns? …I’m trying to figure out the problem before suggesting a solution…

I haven’t deciphered SredniVashtar’s answer, but I’d bet dollars to doughnuts that it’s better than anything I can come up with.

1 Like
6

lol, I’m honestly having a hard time imagining why my response would ever be a good idea either :smiley:

2 Likes
7

I get a data file from an external source. The key field we have is in this file, but isn’t always in the same location. But it IS always named the same.

I want to be able to dump this data as-is into a tab in my file and have my formulas work whether it’s in the first column or the 50th.

8

You’ll definitely need the Key column in the left-most column.
So:

  1. Put the data starting at Column B.
  2. Create an Index/Match in Column A that pulls the Key data column.
  3. Do what you’re doing.

I am doing something somewhat similar. I have data from some source over which I have no control. Columns get moved or renamed every time I paste it (which is infrequent), and I only need, say, seven columns out of the 80.
So, I paste all the data, and to the right of this dataset, I pull only what I want, using your technique, and in the order I want the columns. The formulae are there already from the last time I pasted the data, so any change in a column name is noted by #NA’s. I find what it is now called, change the column title of MY data, and viola! ♪♪
(Now, if a column I want has been deleted, then I have to go ask questions.)

All of this is eliminated if you control your data sources (i.e., extract your own data), which I highly recommend.

1 Like
9

Ohhhhhhhhhhhh. Yeah, what the good doctor said.

10

The only dumb question is the one unasked.

11

Hmmm. Ok. That’s what I currently do. I was hoping to eliminate the leftmost key field and have the lookup columns be ONLY the data file. But maybe it isn’t feasible how I’m envisioning.

12

Well, the “Match” formula only counts from top to bottom, or from left to right.

13

Another solution is to turn whatever gets dumped into a data table. (Insert → table) then your formulas can reference the headers. Not sure how well that works with something so unreliable though.

14

I was going to suggest the use of named ranges.

Let KEY = be the column containing the needed key to reference.

For each column of data, create an short-descriptive name of the column data; for purposes of this illustration, I’ll call it DATA_Y.

Get the new data, update the named ranges as appropriate (not a difficult process, BTW).

then your formula will look something like:
=INDEX(DATA_Y, MATCH(X, KEY, 0), 0)

15

Yeah but I could ask it “which column is my key data in?” And it could tell me it’s the 4th column.

And I could ask it “which column is my lookup data in?” And it could tell me it’s the 15th column

I could also tell it, “please return the corresponding data from whatever column contains my lookup data for this key field (which is contained in the 4th column).”

I just want to be able to tell it “please return the corresponding data from whatever column contains my lookup data for whatever column contains my key data.”

It feels like it should be doable! I even said please!

16

It’s totally doable!

Don’t do it!

4 Likes
17

So what the heck? Someone is giving you data on a regular basis and the columns are in random order?

Assuming there is someone who thinks this is acceptable, tell me more about the format. Does each file have column headers and use some sort of delimiter? Assuming no use of tables (I am not knowledgeable about them), how about sorting horizontally using a custom sort order with your desired key column first. This gets ugly if the columns change.

18

This feels like a challenge I must rise to out of spite at the very least.

3 Likes
19

Why is it a bad idea? This way I don’t have to manipulate any data. The less I ask analysts to change data the less opportunity for error.

1 Like
20

The data comes from a TPA. Their system has hundreds of output options. Each file we receive is specific to a case. One case might have attributes another doesn’t have. They don’t provide columns that aren’t applicable on a case by case basis. So one case might have 50 columns of output, another might have 60. And our key field is buried to the right of their output.

We have no control over the format or layout.

So we need to be able to use what they give us. Being able to create an automated tool that pulls in exactly what we need and only what we need and does not need manual manipulation is ideal.