XLOOKUP to return nth column?

megera716

Board Regular
Joined
Jan 3, 2013
Messages
150
Office Version
  1. 365
Platform
  1. 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 😁
1741122631466.png



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.
 
Ignoring the last paragraph for the moment, try this
Excel Formula:
=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
Excel Formula:
=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)))
 
Upvote 0
Ignoring the last paragraph for the moment, try this
Excel Formula:
=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
Excel Formula:
=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.
 
Upvote 0
Try
Excel Formula:
=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
Excel Formula:
=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)))
 
Upvote 0
Solution

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top