Cross Table with Values, Index Match?

saadbm · #1 (**permalink**) 04-13-11, 06:23

Hello

Assistance with this exercise would be kindly appreciated.

I tried using a few variatons of Index>Match but couldn't quite get it to work.

I would start Sheet 2 with a list of all the stocks on the left and the fund names on the top. Then I need to populate...

Thanks!

weejas · #2 (**permalink**) 04-13-11, 08:53

I'm not 100% sure how you're trying to get from one to the other in Excel, but with careful use of IF() and ISERROR()/ISNULL(), you could probably get it to work:

Code:

=IF([ISERROR({Match+index/Vlookup expression})/ISNULL({Match+index/Vlookup expression})], "Not Held", {Match+index/Vlookup expression})

saadbm · #3 (**permalink**) 04-14-11, 06:46

Ignoring the "not held," aspect -- whats an effective way to change it from the list format into the cross-table (name?) format?

weejas · #4 (**permalink**) 04-14-11, 07:09

Turn it into a pivot table (Data -> Pivot Table and Pivot Chart Report...).

I don't use them at all, but they work like an interactive cross-tab query.

Internet Services

Web Development

Digital Marketing

Consumer Tech