Formula Help
If I want to compare column 2 of Sheet D to column 2 in Sheet F, and return the value from column 1 in Sheet F to column 1 in Sheet D if 2D and 2F are equal, and not return anything if they are not equal, how would I put that in a formula? I think it's a vlookup but my formula didn't work.
Best Answer
-
Gotta start somewhere, right? :-)
I think you'd probably be good with an INDEX / MATCH formula.=IFERROR(INDEX({Sheet F Column 2}, MATCH([Column2]@row, {Sheet F Column 2}, 0)), "")
Two important notes:
1. Make sure your the column type where you are placing the formula is set to text/number.2. Make sure you use the IFERROR wrapper so that you get a "blank" when the value in Column 2 on Sheet D does not match a value from Column 2 on Sheet F.
Answers
-
Hi, @Caitlin R. How you do this depends a lot on what the values are in Column 2 of Sheet D and Sheet F. Are they numeric? Text? Are they unique, or can they repeat.
One simple way might be to use IF/MATCH. For example, let's say you have Sheet D and Sheet F set up like this:
On Sheet D, in the Primary Column, place this formula:
=IFERROR(IF(MATCH([Column2]@row, {Sheet F Column 2}, 0) > 1, [Column2]@row), "")
This returns a value only when Column 2 on Sheet D matches Column 2 on Sheet F.BUT, if your column has duplicate values, this may not work. In that instance, you may need a version of IF/INDEX/MATCH or INDEX/COLLECT, but how that would work depends on the data / column types you have.
-
Thanks @Danielle Arteaga ! That got me something, which is more than what I had :)
2D is a 7-digit numerical value that may repeat in Sheet D but not in Sheet F, which is what I want Sheet D to compare to. (2F is also a 7-digit numerical value) If they are the same I want 1D to return the numerical value in 1F. All numbers.
However, the return is saying #invalid column value
-
Gotta start somewhere, right? :-)
I think you'd probably be good with an INDEX / MATCH formula.=IFERROR(INDEX({Sheet F Column 2}, MATCH([Column2]@row, {Sheet F Column 2}, 0)), "")
Two important notes:
1. Make sure your the column type where you are placing the formula is set to text/number.2. Make sure you use the IFERROR wrapper so that you get a "blank" when the value in Column 2 on Sheet D does not match a value from Column 2 on Sheet F.
-
Thank you! I switched out the first Sheet F Column 2 for Sheet F Column 1, but it is doing what I want it to now. Thank you!!!
-
I have another question….
Same formula as above but now I need it to check a different sheet for the same criteria after it has checked the first sheet. I tried to embed the same iferror but with the different sheet but it's not working. Can you embed an iferror in an iferror?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!