2 hlookup and a vlookup

[Jak7217]

Hey All:

I've got a big workbook and I have simplified my issue with example data below.

Is it possible to have 2 horizontal lookups and a vertical? Here is a snippet of hypothetical data and results. I've used nested index match before, but I can't get it to work here.

Can you write this so that Data is on Sheet1 and Output is on Sheet2? The data will have blank rows and columns periodically, so the formula has to be hearty.

Thanks in advance!

 

[zepharoz]

Hey me again. You can choose to use sumproduct again, but like I said it's a matter that can go beyond me.
In your case here, index match will not work properly since it searches by column and row. Sumifs can work too but may encounter some issues.
=sumifs('Data'!$B$4:$E$6,'Data'!$2:$2,'Output'!$A3,'Data'!$3:$3,'Output'!$B3,$A:$A,C$2)

Try inputting this into Output!C3:
=sumproduct(('Data'!$2:$2='Output'!$A3)*('Data'!$3:$3='Output'!$B3)*($A:$A=C$2)*($B$4:$E$6)
~where 'Data'!$2:$2 is your first names
~'Data'!$3:$3 is your last names
~$A:$A is your years
~$B$4:$E$6 is your data
Then copy and fill the rest of the cells. It should work.

 

[Alex Blakenburg]

Since you have MS365 you have a lot more options.
If you are happy to use formatting to hide the zeroes then this should work:

20231229 2 Way lookup Jak7217.xlsx
	A	B	C	D	E
1	First	Last	2018	2019	2020
2	Bob	A	5	6	7
3	Ron	A	0	0	0
4	Todd	A	0	0	0
5	Bob	B	8	9	10
Sheet2

Cell Formulas
Range	Formula
C2:E5	C2	=XLOOKUP(C$1, Sheet1!$A$3:$A$5, XLOOKUP(1, (Sheet1!$B$1:$E$1=$A2)*(Sheet1!$B$2:$E$2=$B2), Sheet1!$B$3:$E$5, ""), "")

If you don't want to use custom formatting to hide the zeroes you could change it to the this,
PS: Click on the copy icon in the top right corner than paste it into the formula bar in C2

=LET(result, XLOOKUP(C$1,
                                        Sheet1!$A$3:$A$5,
                                        XLOOKUP(1,
                                                         (Sheet1!$B$1:$E$1=$A2)*(Sheet1!$B$2:$E$2=$B2),
                                                         Sheet1!$B$3:$E$5,
                                                         ""),
                                         ""),
            IF(result = 0, "", result))

 

[felixstraube]

Dont know if it will work for you but it seems you just need to transpose you data.

In sheet 2 use:
If you like to hide with formatting:

=TRANSPOSE(Sheet1!A2:E6)

If you like to hide zeros without formatting:

=LET(d, TRANSPOSE(Sheet1!A2:E6), IF(d = 0, "", d))