Excel Spreadsheet

Copper Contributor

Okay.

I have a spreadsheet that needs to be updated Daily/Weekly. (Spreadsheet 1).

(spreadsheet 2) contains the data needed to keep (Spreadsheet 1) updated. How do I pull large significant amount of data from (S2) unto (S1) without re-arranging data in the comments column?

 

I am familiar with Excel, and I also learn better from doing a step by step. So I greatly appreciate if I can get some help on this. Thank you in advance!

3 Replies

@TrayC1201 
You can use VLOOKUP or INDEX and MATCH functions.
VLOOKUP method:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  •  lookup_value: The value you want to look for in the second spreadsheet.
  • table_array: The range of cells in the second spreadsheet where the lookup value is located.
  • col_index_num: The column number in the table_array from which the matching value must be returned.
  • range_lookup: A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match.

If your lookup_value is in the A column of Spreadsheet 2 and the data you want to pull into Spreadsheet 1 is in the B column, you would use col_index_num (2). If you want an exact match, use FALSE for range_lookup.

=VLOOKUP(A2, 'Spreadsheet2'!A:B, 2, FALSE)

 

INDEX and MATCH method:

=INDEX(column_to_return_a_value_from, MATCH(lookup_value, column_to_lookup_the_value_in, 0))
  • column_to_return_a_value_from: The column in Spreadsheet 2 from which you want to pull the data into Spreadsheet 1.
  • lookup_value: The value you want to look for in Spreadsheet 2.
  • column_to_lookup_the_value_in: The column in Spreadsheet 2 where Excel should look for the lookup_value.

The MATCH function returns the position of the lookup_value in column_to_lookup_the_value_in, and the INDEX function uses that position to return the value from the same position in column_to_return_a_value_from.

=INDEX('Spreadsheet2'!B:B, MATCH(A2, 'Spreadsheet2'!A:A, 0))

This formula will look for the value in cell A2 of Spreadsheet 1 in column A of Spreadsheet 2 and return the corresponding value from column B of Spreadsheet 2.

@TrayC1201 

Here's how you can pull data from Spreadsheet 2 (S2) to Spreadsheet 1 (S1) without rearranging data in the comments column, using a step-by-step approach:

Method 1: Using the INDEX & MATCH functions

This method is flexible and allows you to extract specific data based on matching criteria.

Steps:

  1. Identify the data you want to extract: Decide which columns in S2 contain the data you need and which column in S1 you want to place it.
  2. Choose a unique identifier: Identify a column in both S1 and S2 that has unique values for each row. This will be used for matching purposes.
  3. In S1, enter the formula: In the cell where you want the extracted data to appear, type the following formula:
Excel
=INDEX(S2:S2[Number of columns in S2 to extract], MATCH(S1[Unique identifier column], S2[Unique identifier column], 0))
 

Explanation:

  • INDEX(S2:S2[Number of columns in S2 to extract]): This part specifies the range of data you want to extract from S2. Adjust the number of columns based on your needs.
  • MATCH(S1[Unique identifier column], S2[Unique identifier column], 0): This part finds the row in S2 that matches the current row in S1 based on the unique identifier. The 0 specifies an exact match.

Example:

Suppose you want to extract data from columns B and C in S2 based on a unique identifier in column A of both spreadsheets. The formula would be:

Excel
=INDEX(S2:C2, MATCH(A1, S2:A2, 0))
 

This formula will extract the data from the corresponding row in S2 based on the value in cell A1 of S1.

Method 2: Using the XLOOKUP function (Excel 365 only)

This method is newer and offers a simpler syntax for extracting data.

Steps:

  1. Identify the data you want to extract: Similar to method 1, decide which columns in S2 contain the data and where you want to place it in S1.
  2. Choose a unique identifier: Identify a column in both S1 and S2 that has unique values for each row.
  3. In S1, enter the formula: In the cell where you want the extracted data to appear, type the following formula:
Excel
=XLOOKUP(S1[Unique identifier column], S2[Unique identifier column], S2:S2[Number of columns in S2 to extract])
 

Explanation:

  • XLOOKUP(S1[Unique identifier column], S2[Unique identifier column], S2:S2[Number of columns in S2 to extract]): This formula directly searches for the matching value in S2 based on the unique identifier and returns the corresponding data from the specified columns.

Example:

Similar to the previous example, the formula would be:

Excel
=XLOOKUP(A1, S2:A2, S2:C2)
 

This formula will extract the data from columns B and C in S2 based on the value in cell A1 of S1.

Additional notes:

  • Both methods will automatically update the extracted data whenever the source data in S2 changes.
  • You can copy the formula down to other cells in S1 to extract data for multiple rows.
  • If the comments column is in S2, make sure to exclude it when specifying the range of data to extract in the formulas.

Remember to adjust the formulas and cell references based on your specific spreadsheet layout and data needs.

I hope this step-by-step explanation helps!

I understand that after successfully extracting data from S2 to S1, you're facing two challenges:

Data Re-arrangement/Repetition: The imported data from S2 seems to be repeating or rearranging itself in S1.
Comments Transfer: You want the comment column in S1 to show "N/A" for new invoices instead of the repeated values from the formula.
Here are solutions to address these issues:

1. Data Re-arrangement/Repetition:

The reason behind the data repetition likely lies in the formula used. The MATCH function within the formula searches for the value in S1 within a specific range of rows in S2. If multiple rows in S2 contain the same value, the formula might return the same data for multiple rows in S1, causing repetition.

Here are two approaches to handle this:

a) Use Unique Match: Replace MATCH with XMATCH (Excel 365):

If you're using Excel 365, you can leverage the XMATCH function to find the first occurrence of the matching value in S2, preventing repetition.

Update the formula in S1 to:

Excel
=INDEX(S2!$A$1:$[Last Column], XMATCH(A1, S2!$A$1:$A$100, 0, -1))

-1 in the XMATCH function specifies to search backwards from the bottom of the range, ensuring it finds the first matching value.
b) Use Advanced Filter (Earlier Excel Versions):

If you're using an earlier Excel version, you can utilize the advanced filter:

Copy the data from S2 containing the comments.
In S1, select the range where you want to import data (including the comment column).
Go to the "Data" tab and click "Advanced."
In the "Advanced Filter" window, set the "Copy to" range to the selected range in S1.
In the "Criteria range," paste the copied data from S2, ensuring the comment column is included.
Check the box next to the header of the column containing the matching criteria (e.g., the first column in S1).
Click "OK."
This will extract data from S2 based on the matching criteria and copy it to S1, including the corresponding comments from the original location, avoiding repetition.

2. Comments Transfer - N/A for New Invoices:

To display "N/A" in the comment column for new invoices in S1, you can modify the existing formula with an IF statement:

Excel
=IF(ISNA(VLOOKUP(A1, S2!$A$1:$B$100, 2, FALSE)), "N/A", INDEX(S2!$A$1:$[Last Column], MATCH(A1, S2!$A$1:$A$100, 0)))

Explanation:

ISNA(VLOOKUP(...)): This checks if the VLOOKUP function finds any matching value in S2.
IF(ISNA(...), "N/A", ...): If no matching value is found (ISNA returns TRUE), it displays "N/A"; otherwise, it retrieves the data using the INDEX and MATCH functions as before.
Remember to adjust the ranges (e.g., $A$1:$B$100) based on your actual data structure.

By implementing these solutions, you should be able to extract the desired data from S2 to S1 without repetition and display "N/A" for the comment column in new invoices.