List to Table - formula

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
 
A

Ashish Mathur

Hi,

Array enter the following formula (Ctrl+Shift+Enter) in cell G16 and then
copy to the right and downwards

=INDEX($B$4:$D$12,MATCH(1,($B$4:$B$12=$F16)*($C$4:$C$12=G$15),0),3)

I have assumed that the data is in range B4:D12 and in the To.... portion,
the row and column headings are in F16:F18 and G15:I15

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 

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

Top