XLOOKUP not returning value

[Gwill1983]

Hi all,

having real trouble with a lookup not returning a value. I have checked the format of related columns and all are set to number.
I believe the lookup ref is the problem as this is originating from a formula (A3&V3) to combine 2 other numbers to give the row a unique reference. This is in column Y.
If I copy and paste the results in as values then I get the option within the cell to convert the text to a number. As soon as I do this the lookup works perfectly!
does anybody have any idea how I can set this column so that this data is stored as a number with the formula still being used?

 

[kweaver]

It would help if you provided a snapshot of your data (using XL2BB!!) and where the problem is and what you expect as a result.
Since you indicate it's XLOOKUP, I suppose you run 365. It would be helpful if you added that information to your profile.

 

[Saba Sabaratnam]

Other possible solution is to turn your lookup value as text using the following formula.

=XLOOKUP(TEXT(G2,"000"),C:C,D:D)

Kind regards

Saba

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

You have given us very little to go on but given this ..

a formula (A3&V3) to combine 2 other numbers

.. what happens if instead of A3&V3 you use (A3&V3)+0

 

[Gwill1983]

It would help if you provided a snapshot of your data (using XL2BB!!) and where the problem is and what you expect as a result.
Since you indicate it's XLOOKUP, I suppose you run 365. It would be helpful if you added that information to your profile.

Sorry. Didn’t realise this was there to be updated. I will try my best to upload some data, but the content is sensitive. I am on 365 and have updated my profile.

 

[Gwill1983]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

You have given us very little to go on but given this ..

.. what happens if instead of A3&V3 you use (A3&V3)+0

Apologies. I have updated this now.
I will give that a go and let you know if it works.

 

[Peter_SSs]

I have updated this now.

Cheers. Thanks.

 

[Gwill1983]

.. what happens if instead of A3&V3 you use (A3&V3)+0

This worked perfectly. Presumably this forces the cells to format as a number?

 

[Peter_SSs]

Presumably this forces the cells to format as a number?

Yes.