Cross-sheet formula
I would like for sheet to auto populate certain fields based on entering in a "property name"
When I enter a property name, I would like for it to access the fields from another sheet that has that property name, and input values for that property.
would I use a =vlookup?
Thanks for you help!
SGF
Comments
-
A VLOOKUP function would work, but I prefer the flexibility of an INDEX/MATCH. The way that is set up is pretty straightforward. It would look something like this...
=INDEX({Sheet 1 Range 1}, MATCH(Property@row, {Sheet 1 Range 2}, 0))
.
{Sheet 1 Range 1}: Cross sheet reference where you would select the entire column on the first sheet that holds the information you are wanting to display.
Property@row: The cell reference to the column on your second sheet that houses the property name.
{Sheet 1 Range 2}: Cross sheet reference where you would select the entire column that holds the property names.
.
From there you would just update the first range to reference the appropriate column in your fist sheet for the data you are wanting to display ({Sheet 1 Range 1}). Everything else would stay the same.
There are a few other ways to increase flexibility, but this would be a pretty straightforward approach that will allow you to rearrange the columns and sort the rows on both sheets without having to worry about breaking anything like you would with a VLOOKUP.
-
+1 on the INDEX MATCH suggestion. I used vlookup in excel all the time. Transitioning to index/match wasn't natural for my brain. I finally had to write it out and tape it to my desk but now I can just type it out whenever I need to.
Also want to point out that it matters that you include the "0" (match criteria) at the end of the match statement. It might work without it but not consistently.
-
It definitely took me a little while to get used to the thought process behind INDEX/MATCH, but once it clicked it made life so much easier.
And you are most certainly correct about using the 0 in the MATCH statement.
-
having a hard time gettin my mind around the concept. still saying unparceable. Obviously still missing something.
see attached screenshots.
I appreciate any help.
SGF
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!