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?

Successful Formula : =VLOOKUP("Vicki Wasko", {Credit Card Numbers Range 1}, 2, true)

Main Sheet

Reference Sheet

• ✭✭✭

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.

• ✭✭✭

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!