Pull data from a sheet to another with multiple columns

KeeMA

Hi,

I am new to Smartsheet, and trying to see if anyone can help me with the formula.

I am trying to pull a value from row spilt in multiple columns to a different sheet. Product name is the Primary column in both sheets and I need information pulled from various columns Customer LOT.

Each row has one value only in one of these columns, but how to scan for the data in the columns?

VLOOKUP works well when data is in one column.

AdamSYNH

Hi @KeeMA

Assuming the other columns are truly blank, you could add a helper column to join them together and then INDEX/MATCH (more efficient) or VLOOKUP to pull the data from that column using your primary column as your reference value. The blanks would just be nothing added to the helper column, leaving you with the customer lot # from the one column with a value in it.

Your formula for your helper column would be as follows:

=JOIN([Customer Lot # HTOL]@row, [Customer Lot # PTC]@row, [Customer Lot # bHAST]@row, [Customer Lot # ELFR]@row, [Customer Lot # ORM]@row)

INDEX/MATCH formula would be as follows:

=INDEX({Helper column},MATCH([Product Name]@row,{Product Name column}))

AdamSYNH

Hi @KeeMA

Assuming the other columns are truly blank, you could add a helper column to join them together and then INDEX/MATCH (more efficient) or VLOOKUP to pull the data from that column using your primary column as your reference value. The blanks would just be nothing added to the helper column, leaving you with the customer lot # from the one column with a value in it.

Your formula for your helper column would be as follows:

=JOIN([Customer Lot # HTOL]@row, [Customer Lot # PTC]@row, [Customer Lot # bHAST]@row, [Customer Lot # ELFR]@row, [Customer Lot # ORM]@row)

INDEX/MATCH formula would be as follows:

=INDEX({Helper column},MATCH([Product Name]@row,{Product Name column}))

KeeMA

Thank you Adam, it worked.