Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 11:00 AM (PST)
Microsoft Tech Community
SOLVED

How to return a single variable from various variables

Copper Contributor

Hi, my spreadsheet contains various fields that require various inputs which I am trying to automate. I am only trying to automate certain fields. 

 

There are 18 sites and 4 departments and I'm trying to find a way that if I enter the site into a cell, the other selected cells automatically fill in information like the set criteria below. 

 

I tried the IF function but it wouldn't let me enter more than one site.

 

Can you help?

Many thanks in advance,

Sam

SiteDepartmentPhoneField1Field2Field3
AldermastonSalesYesOctOctBG964
SkegnessAdminYesSqdSqdBG654
SkegnessSalesYesNovNovBG395
BromleyHead OfficeYesRmnRmnBG

462

 

8 Replies
It sounds to me that you are looking for the XLOOKUP formula. But, maybe if you upload your file I will be able to see what you are actually trying to achieve there.

Hi, this perhaps

 

=FILTER(A2:F5,((H2=A2:A5)+(H2=""))*((I2=B2:B5)+(I2="")),"Nothing")

2023-03-02 00_48_39-Window.png

 

@sbliss304 

Hi @ricardojose,

 

I've uploaded a file so you can see the layout and fields. There are 2 worksheets:

 

Users are where I would want the values populated.

LOOKUP are the values I would like used used for each office.

 

What I'm trying to achieve is that, if enter the Site into the user worksheet it pulls through the other fields of data relating to that office from the LOOK-UP worksheet.

 

Thank you for helping me with this!!!

Best,

Sam  

Unfortunately you can't use a Table on the "Users" sheet if you want to use FILTER(). You have to convert the table to an "ordinary area " (unsure about the translation, but something like:
table design->convert to area)

Then you can put a formula like this in E2 and copy it down.
=FILTER('LOOK-UP'!$C$2:$N$19,C2='LOOK-UP'!$A$2:$A$19)
By the way: You are allowed to use XLOOKUP in a table as suggested by @ricardojose.
Something like this in E2, copy to the right (assuming VM/BD in the header of column O/N is just a slipp)
=XLOOKUP ([@Site],'LOOK-UP'!$A$2:$A$19,'LOOK-UP'!C$2:C$19,,0)
I prefer Filter(). But this alows you to keep your structure
Hi, thank you, I'll give the XLOOKUP a try and let you know how I get on.
best response confirmed by sbliss304 (Copper Contributor)
Solution

Thank you for uploading the file.

 

I have added the formula and I have also made some changes that I think are the ones you were looking to have using the data validation.

 

@sbliss304 

Hi @ricardojose, thank you so much for your help. It's working like a dream.

1 best response

Accepted Solutions
best response confirmed by sbliss304 (Copper Contributor)
Solution

Thank you for uploading the file.

 

I have added the formula and I have also made some changes that I think are the ones you were looking to have using the data validation.

 

@sbliss304 

View solution in original post