Auto populating data from another sheet

kkeung84246

Hi,

What is the best formula to use for auto populating a same field data from another sheet based on a project ID #?  Please advise.

Thanks

Paul Newcome

I would suggest an INDEX/MATCH. Here's the basics of how this works...

.

INDEX is used to display data from within a range after specifying the row number and optionally the column number.

=INDEX(data_range_to_pull_from, row_number, [column_number])

 

data_range_to_pull_from: This is the range that houses the data you want to display.

row_number: This is a numeric value that will determine the row number within the range to pull from. If there is only one row, enter 1.

[column_number]: This is optional. If your data range is multiple columns, this would be a numeric value to designate which column to pull from.

.

MATCH is used to produce a numeric value of where within a range a specific search value is found. The range is treated as a grid. 1 would be the top left cell within the range. It will then go left to right and top to bottom.

=MATCH(search_value, range, [search_type])

 

search_value: This is where you specify what value you are wanting to search for within the MATCH function. This is where we leverage your unique ID.

range: This is the range that you want to search for your value in. In this case, this will be where your unique ID is housed.

[search_type]: This is optional. I always recommend using the value of 0 (zero) for this as it will look for an exact match. This ensures the highest level of accuracy.

.

So basically we will be using the INDEX function to pull the data and the MATCH function to determine which row to pull it from based on your unique ID. Since you are doing this on another sheet, I will show it as cross sheet references.

 

To create a cross sheet reference, simply click the link to "Reference Another Sheet" in the helper box that appears under your formula as you type whenever you get to the appropriate section of the formula. 

 

My example will specify what exactly to select within the range names as "Sheet: Range}.

 

=INDEX({Original Sheet: Column to Display}, MATCH([Formula Sheet: Unique ID]@row, {Original Sheet: Unique ID Column}, 0))