XLOOKUP to return nth column?

[megera716]

I'm doing this with INDEX/MATCH right now, but I'm wondering if XLOOKUP would be better/cleaner as all my parentheses are getting hard to follow....

This is part of a much longer formula, but it's the part I need help with :
(INDEX(INDEX('Reference 2025'!$A$3:$P$123,0,MONTH([@Date])+4),MATCH(1,([@Name]='Reference 2025'!$A$3:$A$123)*([@Location]='Reference 2025'!$B$3:$B$123),0)))

In the Date column is a date (wowwww, haha), right now I'm working on February, which is month 2. For February's data, what I want to return is in 'Reference 2025' Column F (which is the 6th column, starting from A). 2+4 = 6, so that's what the underlined part accomplishes and then I don't have to update the column reference manually every month. When I'm working with March's data, that will be month 3 and what I want to return is in Column G (#7) and so on.

I wrote an XLOOKUP like this:
=XLOOKUP(1,('Reference 2025'!$A$3:$A$123=[@Name])*('Reference 2025'!$B$3:$B$123=[@Location]),'Reference 2025'!$F$3:$F$123)

Can I automate the bolded red part to work like the MONTH function in my INDEX/MATCH to return the nth column (I have them numbered across the top if that helps) rather than a static column? If I can't, I'll just keep using INDEX/MATCH but if anyone can help me with this, it's all y'all

ALSO. I have some people's names who have more than one row in Reference (each with a different location) so I need to use that criteria, but if they don't have more than one or the location on Reference doesn't match the location in the data, I want it to just use their name. Right now, I've got someone whose location in the data is ABC but in Reference, he only has one row and location is XYZ, and the XLOOKUP is throwing an error.

 

[Peter_SSs]

Ignoring the last paragraph for the moment, try this

=XLOOKUP(1,('Reference 2025'!$A$3:$A$123=[@Name])*('Reference 2025'!$B$3:$B$123=[@Location]),INDEX('Reference 2025'!$A$3:$P$123,0,MONTH([@Date])+4))

or this slightly shorter version of it

=LET(d,'Reference 2025'!$A$3:$P$123,XLOOKUP(1,(INDEX(d,0,1)=[@Name])*(INDEX(d,0,2)=[@Location]),INDEX(d,0,MONTH([@Date])+4)))

 

[megera716]

Ignoring the last paragraph for the moment, try this

=XLOOKUP(1,('Reference 2025'!$A$3:$A$123=[@Name])*('Reference 2025'!$B$3:$B$123=[@Location]),INDEX('Reference 2025'!$A$3:$P$123,0,MONTH([@Date])+4))

or this slightly shorter version of it

=LET(d,'Reference 2025'!$A$3:$P$123,XLOOKUP(1,(INDEX(d,0,1)=[@Name])*(INDEX(d,0,2)=[@Location]),INDEX(d,0,MONTH([@Date])+4)))

Peter, thank you! They both work great, but I think I'd go with the XLOOKUP because, although shorter, the second one looks more complicated, lol.

Regarding the last paragraph of my OP, should I just wrap your XLOOKUP in an IFERROR and tell it to lookup only off of name if the XLOOKUP with both criteria is an error? I wondered if there was something like an approximate match to make it work without another formula.

 

[Peter_SSs]

Try

=XLOOKUP(1,('Reference 2025'!$A$3:$A$123=[@Name])*('Reference 2025'!$B$3:$B$123=[@Location]),INDEX('Reference 2025'!$A$3:$P$123,0,MONTH([@Date])+4),XLOOKUP([@Name],'Reference 2025'!$A$3:$A$123,INDEX('Reference 2025'!$A$3:$P$123,0,MONTH([@Date])+4)))

or this considerably shorter version of it

=LET(d,'Reference 2025'!$A$3:$P$123,a,TAKE(d,,1),c,MONTH([@Date])+4,r,INDEX(d,0,c),XLOOKUP(1,(a=[@Name])*(INDEX(d,0,2)=[@Location]),r,XLOOKUP([@Name],a,r)))