How to combine: INDEX + MATCH + ?VLOOKUP?

Question

I'm having an issue with INDEX + MATCH combination:

=INDEX(ALL!$C$1:$I$1,MATCH(TRUE,ALL!C2:I2<>0,0))

At the moment the aforementioned formula does this job to an extent, where if it finds <>0 value in a row it will return header from this specific column. The issue is that the ROW (as above C2:I2) needs to be specified.

I need to vlookup values in the column "A" in sheet "ALL" and based on that, look at corresponding rows between C:I and if the value in that specific row is <>0 then return heading value.

So, in green I would need a formula to pick up numbers from "Data Source" headings, based on value 1 or any value <>0. I'm guessing it all leads somehow to some sort of "vlookup" hybrid.

Any ideas how to combine vlookup in it?

Thanks

Could you please provide some examplary data of the data input and desired output? I know you have described it in the question, but I'm not sure if I understood it properly (ideally edited into the original question) — Samuel Hulla
I think you need to clarify whether you need to look in just one row or several rows and what happens if there is more than one match. — Tom Sharpe
@Rawrplus - done. The list is dynamic and changes, thus ideally we can look at the entire columns. There is no duplicates at all. — Westra
You can use SUMIF if there is only one '1' per row, you would need something like =SUMIF(INDEX(B:E,MATCH(G2,A:A,0),0),">0",$B$1:$E$1) but I won't have time to post it properly till later. — Tom Sharpe

Samuel Hulla Samuel Hulla · Accepted Answer · 2018-08-16T10:52:00

A simple =SUMIF() formula will do, no other convoluted INDEX() and MATCH() nested formulas required.

Let's presume we have a data-table that starts at B2 and end at F6, like this:

So now, to comprehend the solution, here's the syntax of SUMIF() formula (Function):

=SUMIF( range, criteria, [sum_range] )

So, what we want to do is:

go over the range of C3:F3 (and each other respective row)
the criteria to when to sum, is when this range contains 1
and we want to sum_range (sum up) fixed array of numbers, so $C$2:$F$2

So the result is (for row 3):

=SUMIF(C3:F3,1,$C$2:$F$2)

and we drag the formula down, producing expected result:

PS: I think this illustrates the point very well, as to why it's important to declare not only what your formula is doing but also, what you're trying to as in whole as there often is a better (easier) way to implement something, that you might not have thought of.

In other words, follow the Minimal, Complete and Verifiable Example

How to combine: INDEX + MATCH + ?VLOOKUP?

2 Answers