Index Match Formula Shows Wrong Result

0
votes

I have two table, this one is the initial table that contains raw data (on Sheet 2)

Raw Data

And the second table (on Sheet 1) contains formula based on data from first table

Result Data

I use this formula to calculate the data, but as we can see on the picture, it doesn't produce right result. Could you please help me to modify the formula?

=IFERROR(INDEX(Sheet2!$E$2:$E$12,MATCH(Sheet1!$B$1&Sheet1!B$2&Sheet1!$A3,Sheet2!$C$2:$C$12&Sheet2!$B$2:$B$12&Sheet2!$D$2:$D$12,0)),"")
2
excelindexingexcel-formulamatch
I'd use an auxiliar column on the raw data joining the 3 keys to find a value: user, date and time. Then a simple VLookUp would do the trick – Damian
could you please show the example? – Newbie
In B3 put: =IFERROR(INDEX(Sheet2!$E:$E,MATCH(1,INDEX((Sheet2!$B$1:$B$12=B$2)*(Sheet2!$D$1:$D$12=$A3)*(Sheet2!$C$1:$C$12=$B$1),),0)),"") and drag right and down...btw: merged cells are a pain in the ***, rather unmerge =) – JvdV

2 Answers

0
votes

First the auxiliar column, using the concatenate operator & :

Raw data

Then the formula would be:

=VLOOKUP(B$2&$E$1&$A3;Sheet2!$A:$G;6;0)

Change 6 for 7 if you want the description instead of Activity.

0
votes

Please try this formula. It should go into cell Sheet1!B3 where it must be confirmed with Ctl+Shift+Enter because it's an array formula. (017)

 =IFERROR(INDEX(Table,MATCH(1,(INDEX(Table,,3)=$A$1)*(INDEX(Table,,2)=B$2)*(INDEX(Table,,4)=$A3),0),5),"")

In preparation of this formula to work you need to set up a named range by the name of "Table" which comprises of Sheet2!A2:Fxx. Better set this range up dynamically so that it expands as you add more data but you can also declare it as Sheet2!A2:F1000 where 1000 is a number of rows you expect never to need.

This table has 6 columns, A:F which I intentionally made to include column A, which you don't need so that range columns and sheet columns are identical. Table,,3 simply defines the 3rd column. You can replace it with Sheet2!$C$2:$C$1000. If you do, make sure that all your ranges have identical sizes.

The 5 near the end of the formula, at ,0),5),"") identifies the 5th column of the range Table from which the result is returned if the 3 criteria match. Change this number to 6 to return the result from column F or to 1 if you ever need the value from column A.