Compare 2 columns and retreive more than one column value

[Pavan Renjal]

Hi, I want to compare 2 column values in 'E' with values in 'A' and retreive all the column values from column 'A'. For example:

Source:

A B C D
a 2 3 4
d 6 6 7
c 9 9 9
z 1 2 4

Destination:

E F G H
a 2 3 4
c 9 9 9
z 1 2 4

If i want to lookup a value in column 'E' with value in column 'A' i want retrieve all the values from A to D which i am not able to do with a simple vlookup function.

[FDibbins]

a simple vlookup should work here, make sure you use a 0 (or false) as the last argument, not 1 (or true). Use 1 (TRUE) if the list is sorted, 0 (FALSE) to return an exact match in unsorted data

[ramananhrm]

Are you looking for this result?

[Pavan Renjal]

Are you looking for this result?

Thanks ramananhrm! Thanks for all your effort! But i want to know if there is any way that these formulas are applied on just the column 'E' and then retreive all the column values from A to D instead of applying formulas on multiple columns (in this case columns 'F', 'G', 'H')?

[Pavan Renjal]

a simple vlookup should work here, make sure you use a 0 (or false) as the last argument, not 1 (or true). Use 1 (TRUE) if the list is sorted, 0 (FALSE) to return an exact match in unsorted data

Could you please explain a bit more? Thanks!

[ramananhrm]

If im not wrong, this should be the result.

Rgds,

[FDibbins]

	A	B	C	D	E	F
1	a	2	3	4	a	2
2	d	6	6	7	c	9
3	c	9	9	9	d	6
4	z	1	2	4	z	1

F1=VLOOKUP(E1,$A$1:$D$4,2,0)

[Pavan Renjal]

	A	B	C	D	E	F
1	a	2	3	4	a	2
2	d	6	6	7	c	9
3	c	9	9	9	d	6
4	z	1	2	4	z	1

F1=VLOOKUP(E1,$A$1:$D$4,2,0)

Hi FDibbins,

This will fetch me only values for F column from column B. What i am looking for is, i want to compare values in 'E' column with 'A' column. If there is a match, values in corresponding columns B,C and D should be appearing in F, G and H columns respectively. Is there any way to achieve this by using formula in 'E' column? Thanks for your patience and support!

Regards,
Pavan Kumar