formula help

J

Joe

Hello

I am trying to put a list of data into a table. I would like to use a
formula to do this as the list is updated on a regular basis.

If the ‘data’ was numeric then I could use a pivot table to sum or count but
it is text therefore the pivot table will not work.

I have tried using Vlookup and INDEX but I can’t seem to get the formulas to
work.

below is an example of what i have got and what I wish to create

Can any body help?

Thanks

From…….. To.......
ROW COL DATA DEV ASS COU
AAA DEV RS AAA RS KS PS
AAA ASS KS BBB YS RT KS
AAA COU PS CCC PX IU MN
BBB DEV YS DDD BG TG AC
BBB ASS RT EEE YS EX AC
BBB COU KS
CCC DEV PX
CCC ASS IU
CCC COU MN
DDD DEV BG
DDD ASS TG
DDD COU AC
EEE DEV YS
EEE ASS EX
EEE COU AC
 
S

Sheeloo

Assuming your from data is in A1:C17

and you have 'to' data somewhere with DEV, ASS, COU across columns (in
F1:G1) and AAA, BBB, down rows (in E2:E7) as shown by you

Enter this formula in the cell F2 (having AAA to its left and DEV above it)
=INDEX($C$2:$C$16,MATCH(1,($A2=$A$2:$A$16)*(F$1=$B$2:$B$16),0))
press CTRL-SHIFT-ENTER as its an ARRAY formula

Copy it across then down to get what you want.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top