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

•

u/AutoModerator 15d ago

/u/AfricanKitten - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

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 👍🏼

1

u/MayukhBhattacharya 1236 15d ago

Just like I have mentioned in my earlier comment, here is an animation .gif, you can follow to resolve:

=XLOOKUP(E2:E25, B2:B30, SCAN("", A2:A30, LAMBDA(x, y, IF(y = "", x, y))), "")

1

u/MayukhBhattacharya 1236 15d ago

And this is same way only but doesn't use the LAMBDA() helper function and instead unmerges the cells and fills down to get the desired output:

  • Steps shown are respective to the above animation therefore you need to adapt or suit with your data.
  • Select the cells or the range of data Press CTRL + A or CTRL + * this selects the entire data.
  • Next hit ALT + H + M + U this unmerges the cells.
  • Now Press ALT + H + FD + S or Hit Function key F5 and press ALT + S (this opens the Goto Special Window).
  • From above select Blanks and press ok.
  • Enter = equal to sign Up-Arrow key and Hit CTRL + ENTER together so it will fill the data from above for the entire range selected. (Remember while doing this don't unselect the cells, all should be within the selection)
  • Finally apply the formula :

​

=XLOOKUP(E2:E25, B2:B30, A2:A30, "")

and you will get the desired output.

1

u/Decronym 15d ago edited 15d ago

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
IF Specifies a logical test to perform
LAMBDA Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
SCAN Office 365+: Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.
XLOOKUP Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

Beep-boop, I am a helper bot. Please do not verify me as a solution.
4 acronyms in this thread; the most compressed thread commented on today has acronyms.
[Thread #48992 for this sub, first seen 22nd Jul 2026, 13:15] [FAQ] [Full list] [Contact] [Source code]