megera716
Board Regular
- Joined
- Jan 3, 2013
- Messages
- 150
- Office Version
- 365
- Platform
- Windows
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.
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.