Share via

variable xlookup

Anonymous
2023-05-10T09:20:50+00:00

i have a number in cell AR20 and AR21 - these numbers will change when my spreadsheet is in use so lets assume my numbers initially are 21 and 22 respectively

I have a look up chart in B20 to AL56

i want Xlookup to read AR20 [no. 21] down column B - that is easy enough[ Xlookup(AR20,B20:B56) but i want it to return the answer from the 22nd column [as indicated by cell AR 21] - i can't put in X20:X56 as the look up cell range as it is variable for each calculation

How do i use Xlookup to achieve that variable look up [i need xlookup as the numbers in B are NOT and cannot be in sequential order]

in effect i want Xlookup(AR20,B20:B56,AR21 the 22nd column] but i can't work out how to achieve this

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Lz365 38,201 Reputation points Volunteer Moderator
2023-05-10T10:34:37+00:00

Pete,

One thing to note re.:

=INDEX(XLOOKUP(M1,B1:B10,B1:K10),N1)

This "accidently" works and TBH I have no idea why. Actually this must be (I will edit my previous post...):

=INDEX(XLOOKUP(M1,B1:B10,B1:K10),,N1)

Anyway, this gives you 3 options (you can mark several posts as solution on this site)

Nice EOD...

Was this answer helpful?

1 person found this answer helpful.
0 comments

8 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lz365 38,201 Reputation points Volunteer Moderator
    2023-05-10T09:54:28+00:00

    On reflection, doable with XLOOKUPs only but more complex. With the same setup as above:

    =XLOOKUP(N1, SEQUENCE(,COLUMNS(B1:K10)), XLOOKUP(M1,B1:B10,B1:K10))

    Was this answer helpful?

    0 comments
  2. Lz365 38,201 Reputation points Volunteer Moderator
    2023-05-10T09:41:41+00:00

    XLOOKUP is also available in 2021

    You can't do it with XLOOKUP only. Either you use CHOOSECOLS (365 only) or INDEX to get the value in your 22nd column. Example with my data in B1:K10 and my variables in M1 & N1 (your AR20 & AR21):

    ![Image](https://learn-attachment.microsoft.com/api/attachments/4a9e0a00-f991-4f48-bac3-c7824bbe6644?platform=QnA

    Was this answer helpful?

    0 comments
  3. Anonymous
    2023-05-10T09:28:40+00:00

    365 - as far as i am aware only Excel 365 can use xlookup

    Was this answer helpful?

    0 comments
  4. Lz365 38,201 Reputation points Volunteer Moderator
    2023-05-10T09:26:49+00:00

    Hi Pete

    What version of Excel do you actually run (2021 or 365)?

    Was this answer helpful?

    0 comments