cglf12345
cglf12345

Reputation: 21

How to find a value in one column in another column and return cells in row where value was found

I want to find a value in column E, then get values from same row from columns B, C, and D. So I want to find 1 in column E, and the values from B, C, and E, then find 2 in column E and values from B, C, and D and all the way through 18.

I have tried VLOOKUP, INDEX/MATCH

B       C        D       E

1       4        365     3

2       5        464     2

3       3        151     15

4       4        417     1

5       4        284     7


F       G        H        I

1       4        4        417

2       2        5        464

3       1        4        365

Upvotes: 0

Views: 433

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

G2: =INDEX($B$2:$E$6,MATCH($F2,$E$2:$E$6,0),1)
H2: =INDEX($B$2:$E$6,MATCH($F2,$E$2:$E$6,0),2)
I2: =INDEX($B$2:$E$6,MATCH($F2,$E$2:$E$6,0),3)

and fill down as far as needed, adjusting the array address as required.

enter image description here

Upvotes: 1

Related Questions