Xlookup referencing text values

Surisam

New Member
Joined
Mar 9, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
563
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 9, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 9, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
B6 is looking in this column Can'!$B$2:$B$495,'Consolidated
 

Surisam

New Member
Joined
Mar 9, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Feb 23, 2021
Messages
563
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 9, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,474
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,387
Messages
5,636,031
Members
416,892
Latest member
Bensch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top