Index Match to return matched values from a list

[Spurredon]

I have a list below and want to return all matching results in column B into column D. Also, I wanted to copy down the formula in column E to return all the results against every name that matches. The length of the lists do not match and there is a blank column but this does not matter as it a real life scenario I am trying to replicate. I have tried INDEX and INDEX MATCH combinations but all the examples only look for one return value. Any help is appreciated to use the correct formula in cell E2 to then copy down for all names and results to match. Many thanks

	A	B	C	D	E
1	Name	Result		Name	Result
2	John	Pass		Davinder
3	Mark	Fail		Mary
4	Davinder	Fail		Helen
5	Ola	Pass		Ola
6	Tom	Pass		Tom
7	Helen	Pass		Mark
8				John
9				Luke

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff New.xlsm
	A	B	C	D	E
1	Name	Result		Name	Result
2	John	Pass		Davinder	Fail
3	Mark	Fail		Mary
4	Davinder	Fail		Helen	Pass
5	Ola	Pass		Ola	Pass
6	Tom	Pass		Tom	Pass
7	Helen	Pass		Mark	Fail
8				John	Pass
9				Luke
Data

Cell Formulas
Range	Formula
E2:E9	E2	=XLOOKUP(D2,$A$2:$A$7,$B$2:$B$7,"",0)

 

[Spurredon]

Great thanks so much it works! Never tried the new XLOOKUP only VLOOKUP. The other good thing is that the XLOOKUP copes with inserting and deleting new columns as well as looking left to right which are limitations with VLOOKUP according to what I have just read. I take it then that this kind of task cannot be done easily with INDEX and MATCH and I was barking up the wrong tree. I feel like I have a new power tool for going forward!

 

[alansidman]

I used Index/Match and it seems to work

Book12
	A	B	C	D	E
1	Name	Result		Name	Result
2	John	Pass		Davinder	Fail
3	Mark	Fail		Mary	#N/A
4	Davinder	Fail		Helen	Pass
5	Ola	Pass		Ola	Pass
6	Tom	Pass		Tom	Pass
7	Helen	Pass		Mark	Fail
8				John	Pass
9				Luke	#N/A
Sheet1

Cell Formulas
Range	Formula
E2:E9	E2	=INDEX($B$2:$B$7,MATCH(D2,$A$2:$A$7,0))

 

[Fluff]

As Alan has shown, it can easily be done with index/match, it can also be done with Vlookup

+Fluff New.xlsm
	A	B	C	D	E	F
1	Name	Result		Name	Result	Vlookup
2	John	Pass		Davinder	Fail	Fail
3	Mark	Fail		Mary		#N/A
4	Davinder	Fail		Helen	Pass	Pass
5	Ola	Pass		Ola	Pass	Pass
6	Tom	Pass		Tom	Pass	Pass
7	Helen	Pass		Mark	Fail	Fail
8				John	Pass	Pass
9				Luke		#N/A
Data

Cell Formulas
Range	Formula
E2:E9	E2	=XLOOKUP(D2,$A$2:$A$7,$B$2:$B$7,"",0)
F2:F9	F2	=VLOOKUP(D2,$A$2:$B$7,2,0)

One of the advantages of the new xlookup, is that it has an in-built error handler if no match is found.

 

[Spurredon]

Thanks for the INDEX MATCH option it's good to know. I know I can use VLOOKUP but prefer the XLOOKUP now I've tried it due to the error handling and other advantages over VLOOKUP. Appreciate the different options offered and all very useful for my Excel toolbox

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[alansidman]

An alternative option is to use Power Query and join the two tables in a Query. If this interests you, post back and I will demonstrate.

 

[parsec]

An alternative option is to use Power Query and join the two tables in a Query. If this interests you, post back and I will demonstrate.

I am working on something similar. Here is my Issue.
Sheet1 (with four Columns )
ItemNumber | Name | QtyOnHand | Actualcount

Sheet2 (With two columns)
ItemNumber | Qty

I am using this formula "=INDEX(Sheet2!$B$2:$B$7,MATCH(A2,$A$2:$A$7,0))" to match the ItemNumber on any cell in both of the Sheets and get the value fro Sheet2 ""Oty" into Sheet1 Actualcount
Some how it has become a difficulty for me to make it work.
if possible I like to INDEX/MATCH, Power Query work
thank you
John

 

[Fluff]

@parsec
Please start a thread of your own for this question. Thanks