XLOOKUP gives N/A

=XLOOKUP(Toke!AI2;Info!Q2:Q13;Info!R2:R13)

Why this formula gives me an answer N/A? How I can make it work? Any other formula?

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

You have a mismatch of data types, probably - attach a workbook, NOT a picture.

It says I cannot add picture but I can see picture in my post. Anyway.. I try to find right quarter

Toke!AI2 = 2022-01
Info!Q2 = 2022-01
Info!R2 = Q1

Datatypes are General

We want you to attach the workbook.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Datatypes are General

That's formatting, not data type. We need to see the workbook. You clearly have some items that are dates and some that are not, hence the mismatch, but we can't work out which is which from a picture.

You are trying to find 2021 quarters in a 2022 list - no matches!!!

Try this:

=LOOKUP(--RIGHT(A2,2),{1,4,7,10},"Q"&{1,2,3,4})

You may need to change commas to semi-colons for your locale.

Sorry, list is short. It continues with 2022-01 and same results

You need to provide realistic sample data! Bear this in mind in future.

Change Info A13 to: 2021-12

there were NO matches in your sample.

It is looking up the month part against the starting months of each quearter (Jan, Apr, Jul & Oct) to return the correct quarter. It's an approximate match, so finds the nearest lower or equal to the lookup value.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.