How could I lookup for two column data and return the other column info?
For example, I want to look up lot number and QC test ID in the table below and return test results and final disposition ready?
Answers
-
Are your QC test ID's unique on each row? If so, then you shouldn't really need to also match on the Lot Number.
-
No neither the QC test ID nor the lot number is unique, but combination of both is unique.
Also I tried the simple INDEX MATCH formula for search one column and return one column, but still got "NOMATCH" error even though I know for sure it should has a match.
Formula as below: =INDEX({Over all tracker Lot number column}, MATCH([Lot number]@row, {Overall tracker Result column}, 0))
-
for Index Match function, do I use column only or a large range for the first range and second range? ) I only used columns for both ranges, should I expand?
-
ALso, it looks like I used vlookup function worked....
-
To match on two columns you are going to want to use an INDEX/COLLECT.
=INDEX(COLLECT({Test Result Column}, {QC Number Column}, [QC Number Column]@row, {Lot Number Column}, [Lot Number]@row), 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!