Picking Multiple Values Corresponding to a cell Value in MS Excel 2007

[Jasdeep2107]

Friends,

I have the below mentioned data in the excel workbook "Sheet1"

A1:A10 - CG,CG,CG,EXV,EXV,EMB,EMB,EMB,GSB,GSB
B1:B10: DOZ, JCB, GRD, TP, VCOM, GRD, TP, GRD, COMP, TP


Now in Sheet 2 in different worksheet, I have this data
C1:C10 : EXV,EXV,GSB,GSB,CG,CG,Cg,EMB,EMB,EMB

Now, by using excel formulas, I need to automatically pick the corresponding data of C1:C10 from the B1:B10.

So, my desired result is :
D1:D10: TP, VCOM,COMP, TP, DOZ, JCB, GRD, GRD, TP, GRD.


Hopefully, I have made my query clear, In case you need any further information, please let me know.

Thanks,
- Jassi

 

[flipcube]

what sheet do you want the results on D1:D10 on?

 

[Gettingbetter]

Hi, Welcome to the board.

Put in D1

=vlookup(C1,Sheet1!$A$1:$B$10,2,false)

And copy down

 

[flipcube]

looking at your data, what you want to do is lookup up the values in sheet2 C1:C10 in table A1:A10 and return the corresponding value from B1:B10. Place the results in D1:D10.

This this correct?

One issue I see is that there are multiple B1:B10 values for one A1:A10 value. meaning EXV has to values TP and VCOM. if you lookup EXV in A1:A10, it will find the first occurance of EXV and always give you TP

 

[Jasdeep2107]

It is another worksheet in the same excel file. Vlookup doesn't help since it fetches the first value against an activity code.

We need the look up range to change dynamically to fetch all the cell data in B1:b10 corresponding to a single activity in a1:a10