Which Formula To Use to Return a Value from Another Sheet with Multiple Criteria

Dave French

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

Hollie205

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.

Hollie205

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.

Dave French

Thank you Hollie. This worked. Much appreciated!