Which Formula To Use to Return a Value from Another Sheet with Multiple Criteria
I have a sheet in which I'm trying to create a formula to return a value from another sheet, based on an input in another column. I have successfully used VLOOKUP to accomplish this using one criteria, however, there are multiple criteria options. Meaning, depending on the name input into the "Card Holder Name" column, I would like the "Card Number" column to automatically input the corresponding card number from the reference sheet. I'm not familiar using VLOOKUP or INDEX(MATCH). Can anyone assist?
Thank you in advance!
Successful Formula : =VLOOKUP("Vicki Wasko", {Credit Card Numbers Range 1}, 2, true)
Main Sheet
Reference Sheet
Best Answer
-
I typically use an Index(Collect in place of a VLookup as it allows me to move columns on my sheet without messing up my formula. It would look like this.
=INDEX(COLLECT({Card Number},{Card Owner},[Card Holder Name]@row, next criteria range, next criteria),1)
You can add as many criteria ranges and criteria as you would like just separate with commas. You can either enter the criteria as I have listed above referencing @row or you can put specific data in quotes. You will have to create the references for Card number and Card Owner on your other sheet.
Answers
-
I typically use an Index(Collect in place of a VLookup as it allows me to move columns on my sheet without messing up my formula. It would look like this.
=INDEX(COLLECT({Card Number},{Card Owner},[Card Holder Name]@row, next criteria range, next criteria),1)
You can add as many criteria ranges and criteria as you would like just separate with commas. You can either enter the criteria as I have listed above referencing @row or you can put specific data in quotes. You will have to create the references for Card number and Card Owner on your other sheet.
-
Thank you Hollie. This worked. Much appreciated!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!