Excel INDEX MATCH from varying tables of data

The Sandbox Software/Technology
1

I have four tables of data for [Male, Female] x [Smokers, Non-Smokers].

For the purposes of this thread, let’s say that it’s mortality data by attained age and BMI.

In another sheet, a user will enter “Male” or “Female,” “Smoker” or “Non-Smoker,” age, and BMI.

I know how to do an INDEX MATCH for just one table, but I don’t know how to do that for one of four tables.

Right now the only idea I have is to do the INDEX MATCH (based on age & BMI) on all four tables, then, select from those four results based on sex & smoker status.

Do you have any better ideas?

I have no control over the tables – their layout, their disjointedness, nothing. I can’t combine them into one or anything like that. I can’t change anything about them.

2

I am not sure what your limits are. I guess you can’t do this.. Maybe you can only use 1 cell for your answer, so you can’t “combine” by listing them sequentially in a new range somewhere else. Can you use INDIRECT and move the 4 options/choices inside the INDIRECT statement? Something something evaluate all choices vs evaluate only the chosen one, not sure about that in Excel.

3

Get better control of the data.
Failing that:

  1. Convert each them into a common-format table, one that you create and control.
  2. Paste your common-format tables together as one table.
  3. Go from there.
1 Like
4

That’s an interesting idea…

In the end, this didn’t have to be fancy, so I just looked up the value from all four tables, put them in a list, and then did a lookup on that list.

It gotterdunn. Would’ve been cooler to do it all in one cell, but I’ll probably stick with what I’ve got. :man_shrugging:

I appreciate the idea. Thanks.

5

no doubt.

Yeah, I don’t wanna be messing with the tables. They are what they are.

I appreciate the idea. Thanks.

6

Can you do a nested if? If(female nonsmoker, index match, if male nonsmoker, index match, if female smoker, index match, index match))) it’s not elegant, but it should work.

7

Yeah, that would work. It would achieve my goal of getting this all in one cell, but, boy, would it be quite inelegant, as you note.

I appreciate the idea. Thanks.

8

Oh, you don’t alter THEIR tables.
You put THEIR tables in four separate tabs.
You put THEIR tables in Columns, say, A-M.
You make YOUR table in , say, Columns P-S (leave some blank columns), with all the data that you want, in the order you want, taking from that original table with “={cell reference}”
Now, you have four tables that have the same format.
Combine those into a single tab using PASTE SPECIAL VALUES.
Then, play all you want with YOUR aggregate table.

As I always advocate: Get your own data.

9

Right. Exactly. I don’t want to do that much work for this particular project. I just want to use it “as is” with no warranty expressed or implied.

Your advice is very good, and I would follow it if this were that important. :toth:

10

Can you name the ranges the tables sit in?

11

Yes, I could.

Go on… :popcorn:

12

I’m thinking you could use named ranges in an INDIRECT within an INDEX(… MATCH).

My Excel skills are rusty or else I’d try to write the formula out here.

Maybe something like…
=INDEX (INDIRECT(InputSex & InputSmoking), MATCH(InputAge, RngAges), MATCH(InputBMI, RngBMI))

…might work? :woman_shrugging:

13

I’ll take a look at it some more tomorrow. Thanks for the idea.

14

I know I’m late to this party and this may not be useful anymore, but what about something like the following (assuming named ranges, but cell references are fine alternatively):

INDEX((MaleSmokerRng,FemaleSmokerRng,MaleNonSmokerRng,FemaleNonSmokerRng),MATCH([arguments for row]),MATCH([arguments for column]),if(Sex=“Male”,1,2)+if(SmokeStatus=“Smoker”,0,2))

This is using the less common “reference” form of the index function, which it’s my understanding is basically made for when you have multiple tables like this.

2 Likes
15

That’s an interesting idea and would probably work. Thanks for sharing it. I’ll consider it next time.

16

I’m also late to the thread… but it may be useful to remember that in Excel you can define named ranges to reference Excel table columns (=Table1[Foo]), or using INDIRECT (=Indirect(“A2:A500”)), or using other formulas (=IF(A1=“Male”,Table1[Foo],Table2[Bar])).

That way you can have a simple-to-read formula…although whoever inherits/audits the spreadsheet will have to know enough to be able to look up the named-range definition.

1 Like
17

:exams:

  • Don’t ever use indirect.

  • Don’t use four separate tabs.

  • Do consider copying their tables to standardize them.

  • Do add some kind of warnings on your tab to notify you if the tables change.

  • But really!!!, your original idea sounded just fine. Calculate it 4 times. It’s just 4 cells, so who cares?

Having the 4 calculations will give you a common-sense check, so that’s good. Unless there’s something terribly slow about the process, in which case you might want to use a long formula like a nested-if (in which case, uses ALT-ENTER, to make it legible.)

1 Like
18

I use alt-enter when I’m commenting on something, I don’t know why I didn’t think about using it in a formula… I never even considered it. Excuse me while I go think about every long formula I’ve ever written and whether I ought to be alt-entering at various points (I generally make the formula bar large, take a screenshot and make notes if I’m struggling with parsing it… ugh…)

1 Like
19

Why does everyone feel compelled to do everything in one formula? It’s not like Excel has a shortage of cells.

  • Cell #1: Determine which table you need.
  • Cells #2 & #3: Determine the row and column you need in that table.
  • Cell #4: Call INDEX(CHOOSE(i, range1, range2, …), row, col)

CHOOSE becomes impractical if you have a lot of tables to, well, choose from. But in that case you should just merge them all into one table and be done with it.

EDIT: If the row and column labels are different in the different tables you can MATCH(x, CHOOSE(), ) too. And MATCH has 3 arguments. ALWAYS. angrily waves cane at kids these days

1 Like