Formula for looking up values from one sheet to another using IF
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.
Answers
-
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
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!