Looking for tips on what would be similar to a VLOOKUP but with the ability to return multiple fields. On excel file 1 is a list of customer names and addresses in my system. Excel file 2 is a list of members of a specific group. So I am trying to confirm if the customers are a member of this group.
I would like excel to search for the customer's address from file 1 on file 2 and copy the whole line of data onto file 1. Then file 1 will have the data from my system and from the membership line, side by side.
There is one field - Member ID - that can be matched from file 1 to file 2 and I have used this field for a XLOOKUP, but it will only return one field and I need the whole line of data. Also, would be ideal if the tool could search by address for customers that do not have a member ID in our system.
Looking for tips on what would be similar to a VLOOKUP but with the ability to return multiple fields. On excel file 1 is a list of customer names and addresses in my system. Excel file 2 is a list of members of a specific group. So I am trying to confirm if the customers are a member of this group.
I would like excel to search for the customer's address from file 1 on file 2 and copy the whole line of data onto file 1. Then file 1 will have the data from my system and from the membership line, side by side.
There is one field - Member ID - that can be matched from file 1 to file 2 and I have used this field for a XLOOKUP, but it will only return one field and I need the whole line of data. Also, would be ideal if the tool could search by address for customers that do not have a member ID in our system.
Share Improve this question asked Feb 7 at 17:12 Data NewbieData Newbie 1 New contributor Data Newbie is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. 3- on the return of XLOOKuP you can specify more columns than one. – Scott Craner Commented Feb 7 at 17:32
- Please provide enough code so others can better understand or reproduce the problem. – Community Bot Commented Feb 8 at 0:42
- @ScottCraner only if the lookup value is a single cell/string, else it only returns the first column. FILTER may also be worth looking into. – P.b Commented Feb 8 at 12:31
1 Answer
Reset to default 0There's a few ways to do this, but you're pretty much right there. Without looking at your sheet I can only speak conceptually so adjust this as needed.
Xlookup allows you to define the return array, but it doesn't specify it needs to be a single column, so be expanding this over multiple columns as long as it finds a match it will return the matching columns for that row. Make sure your Unique identifier is unique or it may yield unexpected results:
=XLOOKUP(G2,A2:A6,B2:C6,0,0)
If you need to move this formula around you will want to lock the search array and return array with F4.
You can use a filter to give an identical result. With this you can give multiple inputs and it will more predictably give the results for each of the inputs.
=FILTER(B2:C6,A2:A6=G2)
This you probably won't want to move since you would feed it either one or multiple results to get the appropriate return. Whereas the lookup you may want to fill a set of columns with the results but have them work independently for sorting purposes.