2007 Formula Needed to Update Table Values

[Flintstone]

I am using Excel 2007 and need help identifying a formula to complete a table
with data from another range of cells. I can explain best through the
following example:

I want to fill in the missing data in the Table 1 with data from Table 2.
Dog / Red needs to be updated with 111.
Cat / Blue needs to be updated with 555.
Monkey / Yellow needs to be updated with 888.

Table #1
Dog Cat Monkey
Red
Blue
Green
Yellow


Table #2
Dog Red 111
Dog Yellow 333
Cat Red 444
Cat Blue 555
Monkey Yellow 888
Monkey Blue 999

I have tried using Index, but Index uses Rows and Columns and I need a
formula that uses Columns and Columns.

Any help is greatly appreciated.

 

[Fred Smith]

If it was me, I would add a column to Table #2 which concatenates columns 1
and 2 (ie, =a1&a2). Then use a Vlookup in Table #1, as in:
=vlookup(b$1&a$2,table1!$c:$d,2,false)

Regards,
Fred

 

[T. Valko]

I am using Excel 2007

Try this...

Table 1 column headers B11, row headers A2:A5

Table 2 in the range A10:C15

Enter this formula in B2:

=SUMIFS($C$10:$C$15,$A$10:$A$15,B$1,$B$10:$B$15,$A2)

Copy across to D2 then down to B55

If you don't want to see any 0 results I'd use a custom number format of
General;General;

 

[Flintstone]

Thank you. . . I am not familar with the SUMIFS function, but the results
appears to be headed the right direction. I will test some more and then
post an update.

Thanks again!
Keith

 

[T. Valko]

As long as the combinations in table 2 are unique that should work just
fine. For example, in table 2 there is only one instance of Dog + Red.

 

[Herbert Seidenberg]

Excel 2007 PivotTable
No formulas.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/02_23_10.xlsx

 

[Flintstone]

Thanks Biff!

The SUMIFS function is exactly what I was looking for!

Regards,
Keith

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Flintstone]

Herbert,

Thank you for the information, but the link you have provided requires
"download" authentication which I will not do over the internet.

I have used Pivot Tables to compile data from a defined table, but I need to
gather data from other resources and report back in a specific format.

The SUMIFS is a new function for Excel 2007 that appears to satisfy my
needs. Thank you.


Biff - Thanks again!