Trying to return text from a column on different sheet and getting #value error.

[John_k]

Hi I'm trying to pull three pieces of data from a different excel sheet based on a unique code. The 3rd column is text greater than 255 characters and cant get this to work, which appears to be causing issues. The text includes (), ', - characters also.

=XLOOKUP(C72,'AE Level'!$E:$E,CHOOSE({1,2,3},'AE Level'!$G:$G,'AE Level'!$Z:$Z,'AE Level'!$AA:$AA,'AE Level'!$GH:$GH))

Can anyone help or provide a better way to get the data from the third column.

Thanks
J

 

[Alex Blakenburg]

Just curious, your choose has is selecting 3 columns but you have 4 columns in the list, is there a reason for that ?
It seems to be choose that is causing the issue. This seems to work.

=LET(rReturn,HSTACK('AE Level'!G:G,'AE Level'!Z:Z,'AE Level'!AA:AA),XLOOKUP(C72,'AE Level'!E:E,rReturn))

 

[John_k]

Just curious, your choose has is selecting 3 columns but you have 4 columns in the list, is there a reason for that ?
It seems to be choose that is causing the issue. This seems to work.

=LET(rReturn,HSTACK('AE Level'!G:G,'AE Level'!Z:Z,'AE Level'!AA:AA),XLOOKUP(C72,'AE Level'!E:E,rReturn))

Sorry that was just a typo its the three columns i need, okay i shall try this you have sent.

 

[John_k]

Sorry that was just a typo its the three columns i need, okay i shall try this you have sent.

also if i wanted to add a fourth column what would i need to do. Thank you

 

[Fluff]

What version of Excel are you using?

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’)

 

[Alex Blakenburg]

also if i wanted to add a fourth column what would i need to do. Thank you

Assuming you have MS365 and the HStack function, this is just a comma separated list of ranges, you can add more if you need more.

HSTACK('AE Level'!G:G, 'AE Level'!Z:Z, 'AE Level'!AA:AA)

 

[John_k]

Assuming you have MS365 and the HStack function, this is just a comma separated list of ranges, you can add more if you need more.

HSTACK('AE Level'!G:G, 'AE Level'!Z:Z, 'AE Level'!AA:AA)

correct MS365 - sorry as you can tell my skill set is poor lol So if i wanted to apply this to multiple line items what should i include? Thank you so much!

 

[John_k]

correct MS365 - sorry as you can tell my skill set is poor lol So if i wanted to apply this to multiple line items what should i include? Thank you so much!

sorry i should have added - the last column i wish to pull is text heavy, is there a character limt here?

 

[Alex Blakenburg]

You mentioned 255 characters so I tested on 300, so it should be fine.

 

[John_k]

You mentioned 255 characters so I tested on 300, so it should be fine.

Thank you!!