XLOOKUP with RIGHT function [closed]

Question

Closed. This question needs details or clarity. It is not currently accepting answers.

Want to improve this question? As written, this question is lacking some of the information it needs to be answered. If the author adds details in comments, consider editing them into the question. Once there's sufficient detail to answer, vote to reopen the question.

Closed 4 months ago.

Improve this question

Is it possible to use the RIGHT function with XLOOKUP?

I have the following formula that refuses to work

=XLOOKUP(RIGHT(A2, 3), Sheet1!$A$3:$A$200, Sheet1!$B$3:$B$200, "")

Any alternatives welcome!

Please clarify your question by editing it so others can reproduce your problem. Suggest you read the HELP pages for information as to How to Ask a Good Question, and How to create a Minimal, Complete, and Verifiable example. — Ron Rosenfeld
– Ron Rosenfeld, Commented Jul 18 at 10:06
What error are you getting? Because it works for me. Also for this to work, check what is the value of RIGHT(A2, 3). This value has to be present in Sheet1!$A$3:$A$200 without any trailing or leading places. It has to be exact. — Siddharth Rout
– Siddharth Rout, Commented Jul 18 at 10:24
Find the cell that you think is the same, e.g., Sheet1!A5, and try the following formula: =RIGHT(A2, 3)=Sheet1!A5. Maybe share what exactly is in the cells. — VBasic2008
– VBasic2008, Commented Jul 18 at 11:06
@cdfj, in that case you are comparing a string against a number most likely! Try: =XLOOKUP(--RIGHT(A2, 3), Sheet1!$A$3:$A$200, Sheet1!$B$3:$B$200, "") — JvdV
– JvdV, Commented Jul 18 at 11:25

rotabor · Accepted Answer · 2025-07-18 11:29:47Z

3

To ensure your data and the search pattern, use

=TEXTJOIN(",",TRUE,CODE(MID(RIGHT(A2,3),SEQUENCE(3),1)))=
  TEXTJOIN(",",TRUE,CODE(MID(Sheet1!A5,SEQUENCE(LEN(Sheet1!A5)),1)))

where 'Sheet1!A5' is expected cell matches the search pattern.

The formula

=TEXTJOIN(",",TRUE,CODE(MID(str,SEQUENCE(LEN(str)),1)))

provides the list of codes for the given string 'str'.

UPD

If you have numbers in the sought range, use

=XLOOKUP(RIGHT(A2, 3), TEXT(Sheet1!$A$3:$A$200,"#"), Sheet1!$B$3:$B$200, "")

Or, vise versa:

=XLOOKUP(VALUE(RIGHT(A2, 3)), Sheet1!$A$3:$A$200, Sheet1!$B$3:$B$200, "")

edited Jul 18 at 11:29

answered Jul 18 at 11:16

rotabor

5,6112 gold badges5 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

cdfj Jul 18 at 11:25

That formula returns TRUE

cdfj Jul 18 at 11:29

Thanks @rotabor =XLOOKUP(RIGHT(A2, 3), TEXT(Sheet1!$A$3:$A$200,"#"), Sheet1!$B$3:$B$200, "") solved the problem!

rotabor Jul 18 at 11:31

It's important to provide full information including data set(s) or data samples.

JvdV · Accepted Answer · 2025-07-18 11:24:30Z

3

Looking at all the comments I'm pretty sure OP is comparing text against numbers.

Formula in D2 as an example:

=XLOOKUP(--RIGHT(A2,3),A4:A5,B4:B5,"")

answered Jul 18 at 11:24

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Collectives™ on Stack Overflow

XLOOKUP with RIGHT function [closed]

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related