Cross-sheet formula

steve50951

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

Paul Newcome

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.

Mike L.

+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. 

Paul Newcome

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.

steve50951

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