Vlookup function to look for similar value but the different return value

[ExcelNewbie2020]

I have a source data (code column) that has duplicate values with different names.. Would it be possible for a Vlookup function combination with other function to look for similar value but the different return value. Please find below expected result.. thank you..

source				expected result
Code	Name			Code	Name
AAA	Lara			AAA	Lara
BBB	Gayle			AAA	Welles
CCC	Ponting			BBB	Gayle
AAA	Welles			BBB	Perot
BBB	Perot			CCC	Ponting
CCC	Murdoch			CCC	Murdoch
DDD	Kipling			DDD	Kipling
EEE	Coolidge			EEE	Coolidge
EEE	Porter			EEE	Porter
GGG	Connery			GGG	Connery
HHH	Beatty			GGG	Fenty
GGG	Fenty			HHH	Beatty

 

[Joe4]

Not sure which of the three listed versions of Excel you are using for this, but if using 365, you can simply use the new SORT function, as that is all it really appears you are doing, i.e.

=SORT(A3:B14)

See: SORT function - Microsoft Support

 

[ExcelNewbie2020]

Not sure which of the three listed versions of Excel you are using for this, but if using 365, you can simply use the new SORT function, as that is all it really appears you are doing, i.e.

=SORT(A3:B14)

See: SORT function - Microsoft Support

using 365.. but i would like is a formula with vlookup function (if possible) that will return the name from the expected result

 

[Joe4]

using 365.. but i would like is a formula with vlookup function (if possible) that will return the name from the expected result

VLOOKUP only returns one record, by its very nature.

Why insist on using a function that doesn't do what you want, when there is one that does, and does it very easily?
I am just trying to understand why the insistence on VLOOKUP. It feels like there might be something important you are not telling us.
Otherwise, it does not seem to make much sense.

 

[ExcelNewbie2020]

VLOOKUP only returns one record, by its very nature.

Why insist on using a function that doesn't do what you want, when there is one that does, and does it very easily?
I am just trying to understand why the insistence on VLOOKUP. It feels like there might be something important you are not telling us.
Otherwise, it does not seem to make much sense.

thanks..actually, it is more like a screening question for problem solving skills. i solved that using other function but insist to use vlookup. Now i am bit curious if it can be really done by vlookup..

 

[Joe4]

thanks..actually, it is more like a screening question for problem solving skills. i solved that using other function but insist to use vlookup. Now i am bit curious if it can be really done by vlookup..

It may possibly be able, but I think it is going to be needlessly complex. I think you would have to find where the first one is found, and then start the second VLOOKUP from that point on, so you don't return the first one again. Might be better to use INDEX/MATCH than VLOOKUP. But if you are trying to lookup multiple values in column A, it could really get crazy (unless someone knows some good "tricks").

IMO, the effort isn't really worth it. I don't think it is a method any reasonable person would ever use, when they are much better options available.
Some of the newer functions, like SORT and FILTER make these sort of questions much easier.

 

[ExcelNewbie2020]

It may possibly be able, but I think it is going to be needlessly complex. I think you would have to find where the first one is found, and then start the second VLOOKUP from that point on, so you don't return the first one again. Might be better to use INDEX/MATCH than VLOOKUP. But if you are trying to lookup multiple values in column A, it could really get crazy (unless someone knows some good "tricks").

IMO, the effort isn't really worth it. I don't think it is a method any reasonable person would ever use, when they are much better options available.
Some of the newer functions, like SORT and FILTER make these sort of questions much easier.

i agree.. thank you