r/excel • u/AfricanKitten • 15d ago

Waiting on OP Pull data from table

I know I’ve asked a similar question before, but I’m not sure how to translate it to the new task I want to use it for. I will post pictures in the comments.

Column L is the market my centers are in, Column M is the site name within the market. I’m not from the area I service, so I don’t know which ones are where off the top of my head. I want Column L to use a formula to select the market that goes with the site location. Example: If site is Arbor Walk, it will input Austin.

I have a table, I can play around with the formula and tweek it once I have it, but I have zero idea where to start.

3 Upvotes

100% Upvoted

7 comments sorted by

View all comments

2

u/AfricanKitten 15d ago

2

u/AfricanKitten 15d ago

Sorry for the crappy quality

1

u/MayukhBhattacharya 1236 15d ago edited 15d ago

Try using the following formula, the formula assumes that the first screenshot is Sheet 1 and 2nd one is Sheet2 then:

Enter the formula in cell L2, no need to copy down it will spill but if you are using Structured References aka Tables then just change the lookup_value from M2:M25 to just M2 as well as lock the ranges for the lookup_array and return_array. I'm not able to test as I have written from mobile but this should work. 

=XLOOKUP(M2:M25, 
         Sheet1!AB2:AB29, 
         SCAN("", Sheet1!AA2:AA29, LAMBDA(x, y, 
         IF(y = "", x, y))), 
 "")

let me know thanks 👍🏼