Nested Lookup or Index,Match or ??

**rwabennett** · 02-27-2013, 08:49 AM

I have a list of Events : AA, AB, AC... each with a number of hours relating to that particular event eg

Event AA : 22 hrs,
Event AB : 11 hrs
etc

Items have been used in different events. I would like to record the total hours that each item has been used in the various events.

I would like the user to insert the letter 'X' corresponding to which event the item was used and this would be used in determing the total hours used.

I have solved my problem for one cell using the Lookup function, but I need it to consider multiple cells. I am unsure how to do this. Perhaps I should use other functions?

I have attached a simple example.

I would appreciate any advice you may have.

Richard

**popipipo** · 02-27-2013, 08:57 AM

Is this what youre looking for?

**benishiryo** · 02-27-2013, 09:01 AM

hi Richard, an alternative array formula:
=SUM(TRANSPOSE(F5:L5="x")*($C$5:$C$11))

you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER. When done correctly, you should see curly brackets surrounding it. and M5 should be = C5+C6+C7+C11?