Formula for looking up values from one sheet to another using IF

jeanniesry_4

Hello,

Would anyone be able to suggest the best formula to use for pulling information from first sheet to a second sheet if the criteria on a column of the first sheet is "yes"?

Basically if Column #48 of Sheet 1 is marked "Yes," then transfer the data from Columns #7 and #9 of Sheet 1 into Sheet 2. I tried an IF / VLOOKUP function but it didn't work. Could IF/INDEX MATCH or IF/COLLECT work? See below for what sheet 1 and sheet 2 looks like.

Bassam Khalil

Hi @jeanniesry_4,

please try the following:

1- you must have unique Row-ID in both Sheet1 & Sheet2.

2- in sheet2 use the following formula to collect the value of column 7 ( Part ) when you check the cell in column 48 in sheet1

=IFERROR(INDEX(COLLECT({Part}, {Row-ID}, [Row-ID]@row, {48}, @cell = 1), 1), "")

3- in sheet2 use the following formula to collect the value of column 9 ( Lot ) when you check the cell in column 48 in sheet1

=IFERROR(INDEX(COLLECT({Lot}, {Row-ID}, [Row-ID]@row, {48}, @cell = 1), 1), "")

the following i a screenshot shows the result:

sheet 1

sheet 2