???????? Column match ??????????

[Bruce Ratcliffe]

What formula can be used to return a value in a column that is next to a column of names and match that with another column where the same names have been rearranged?

 

[cyrilbrd]

Hi, try index(range_to_be _returned, match(value_to_check,Range_where_value_is,0))
note index range and match range should be of the same size example A$17:A$456 and $C$17:$C$456

 

[JoeMo]

Another to try:

Excel Workbook
	A	B	C
1	John	matches cell $C$4	Paul
2	Paul	matches cell $C$1	Tom
3	George	matches cell $C$5	Ringo
4	Ringo	matches cell $C$3	John
5	Jane	no match	George
6			Bill
R40

 

[Bruce Ratcliffe]

Thank you, but what I am trying to do is to take the numbers in one column that are paired with names in another column and pair them again with a third column where the names have been rearranged. Not sure how the above formulas will do that for me?

 

[cyrilbrd]

try:

Excel 2013
	A	B	C	D	E
1	name	number		name	number
2	john	4		adele	5
3	mark	3		joan	9
4	adele	5		john	4
5	phil	7		mark	3
6	joan	9		phil	7
Bruce Ratcliffe

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

 

[Bruce Ratcliffe]

Fantastic!

 

[cyrilbrd]

Fantastic!

Glad you got it sorted out.
Thanks for the feedback.