Xlookup referencing text values

[Surisam]

Hi,

I need assistance in configuring my xlookup formula

Im stuck on figuring out a way on how I can reference cell B6 to look at 1 or 2 multiple text values

=XLOOKUP($J6&$C$1&L$5&$B6&"Text",'Consolidated Can'!$A$2:$A$495&'Consolidated Can'!$C$2:$C$495&'Consolidated Can'!$D$2:$D$495&'Consolidated Can'!$B$2:$B$495,'Consolidated Can'!$G$2:$G$495,0,0)

I need cell B6 to also look at another text value. Not sure if i need to insert an if statement in there?

Any help will be appreciated.

 

[Alex Blakenburg]

Do you mean you want B6 to look at another column in Consolidated ?
Please give an example of the B6&"Text" ? what it the "Text" represent and what columns is it looking up ?

 

[Surisam]

Yes B6 is looking at another column in consolidated. That column in consolidated has different text values.

the consolidated column has different text values such as “west” “cent”

Ex: if B6 is west I want it to match the consildate column it’s looking into with west. If B6 is cent I want it to the same above with cent

so how I write that portion of the formula?

 

[Surisam]

B6 is looking in this column Can'!$B$2:$B$495,'Consolidated

 

[Surisam]

Let me rephrase my ask

Lets assume B6 has the text "west" in it.

I need the value returned from the consolidate file if it see the text value "west" or "cent", but B6 must remain as "west"

Is this possible with Xlookup?

 

[Alex Blakenburg]

B6 is looking in this column Can'!$B$2:$B$495,'Consolidated

Lets assume B6 has the text "west" in it.

I need the value returned from the consolidate file if it see the text value "west" or "cent", but B6 must remain as "west"

I am still a bit confused. The Xlookup needs to know what its looking for.
If B6 contains west your only options are to either look for west or look for west with wildcards either before it or after it or both (west*, *west, *west*).
These will find if the lookup column has west and anything else in the same column. If you want west or cent, you would either need to either put the cent in a different field to B6 and use OR logic or hard code cent into the Xlookup formula which seems an unlikely option.

 

[Surisam]

Do you mean you want B6 to look at another column in Consolidated ?
Please give an example of the B6&"Text" ? what it the "Text" represent and what columns is it looking up ?

I am still a bit confused. The Xlookup needs to know what its looking for.
If B6 contains west your only options are to either look for west or look for west with wildcards either before it or after it or both (west*, *west, *west*).
These will find if the lookup column has west and anything else in the same column. If you want west or cent, you would either need to either put the cent in a different field to B6 and use OR logic or hard code cent into the Xlookup formula which seems an unlikely option.

Thanks for your reply

so based on what you’re saying, I’m unable to return a value

I am still a bit confused. The Xlookup needs to know what its looking for.
If B6 contains west your only options are to either look for west or look for west with wildcards either before it or after it or both (west*, *west, *west*).
These will find if the lookup column has west and anything else in the same column. If you want west or cent, you would either need to either put the cent in a different field to B6 and use OR logic or hard code cent into the Xlookup formula which seems an unlikely option.

thanks for your reply

 

[Peter_SSs]

What about a small set (not 495 rows) of sample data (with XL2BB so we can easily test with it) and explain what you would want returned and why in relation to that sample data?