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.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!