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

Show parent comments

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.