Look Up Multiple Values And Returning One Value
 

I've seen multiple posts on this topic but I couldn't understand any of them (either I am just too new to this or it wasn't what I am looking for).

Someone help!

Example of my excel:
SHEET 1
..........A..........B..........C..........D
1.......STATE....HOT.......COLD....WARM (<<THIS IS THE HEADER)
2.........CA
3.........FL
4.........TX

SHEET 2
..........A............B................C......... .
1.......STATE..CLIMATE....TOTAL CITIES (<<THIS IS THE HEADER)
2.........CA.......COLD.............5
3.........FL........HOT..............6
4.........TX........HOT.............15
5.........CA.......WARM............2
6.........CA.......HOT...............7
7.........FL.......COLD..............3
8.........TX.......WARM.............8

Basically, I want to look up the STATE and the CLIMATE and return the values onto Sheet 1. Is this possible? Feedback is certainly appreciated

Look Up Multiple Values And Returning One Value
 

hi Annie,

in SHEET 1 cellule B2:

=SUMPRODUCT(--(Feuil2!$A$2:$A$10=$A2)*(Feuil2!$B$2:$B$10=B$1)*(F euil2!$C$2:$C$10))

copy B2 to right and down

--
isabelle



Le 2012-06-08 12:31, Annieeee a écrit :
I've seen multiple posts on this topic but I couldn't understand any of
them (either I am just too new to this or it wasn't what I am looking
for).

Someone help!

Example of my excel:
SHEET 1
.........A..........B..........C..........D
1.......STATE....HOT.......COLD....WARM (<<THIS IS THE HEADER)
2.........CA
3.........FL
4.........TX

SHEET 2
.........A............B................C..........
1.......STATE..CLIMATE....TOTAL CITIES (<<THIS IS THE HEADER)
2.........CA.......COLD.............5
3.........FL........HOT..............6
4.........TX........HOT.............15
5.........CA.......WARM............2
6.........CA.......HOT...............7
7.........FL.......COLD..............3
8.........TX.......WARM.............8

Basically, I want to look up the STATE and the CLIMATE and return the
values onto Sheet 1. Is this possible? Feedback is certainly appreciated

Hi Isabelle,

Thanks for your response but I don't understand the formula you used. Could you please explain the logic behind the formula? If I can understand it, I can adjust on my sheet.

Here's the example of my excel again:
SHEET 1
..........A..........B..........C..........D
1.......STATE....HOT.......COLD....WARM (<<THIS IS THE HEADER)
2........CA
3........FL
4........TX

SHEET 2
..........A............B................C......... .
1.......STATE..CLIMATE....TOTAL CITIES (<<THIS IS THE HEADER)
2.........CA.......COLD.............5
3.........FL........HOT..............6
4.........TX........HOT.............15
5.........CA.......WARM............2
6.........CA.......HOT...............7
7.........FL.......COLD..............3
8.........TX.......WARM.............8

I need a formula where SHEET 1 is filled as follows:
..........A..........B..........C..........D
1.......STATE....HOT.....COLD....WARM (<<THIS IS THE HEADER)
2........CA.........7...........5.........2....
3........FL..........6...........3.........0...
4........TX.........15..........0.........8...

Look Up Multiple Values And Returning One Value
 

hi Annie,


there are many tutorials for sumproduct on the net,
i hope you enjoy reading this:

http://www.exceluser.com/explore/sumproduct_11.htm
http://www.meadinkent.co.uk/xlsumproduct.htm
http://office.microsoft.com/en-us/ex...005209293.aspx

--
isabelle



Le 2012-06-11 10:17, Annieeee a écrit :
Hi Isabelle,

Thanks for your response but I don't understand the formula you used.
Could you please explain the logic behind the formula? If I can
understand it, I can adjust on my sheet.

Here's the example of my excel again:
SHEET 1
.........A..........B..........C..........D
1.......STATE....HOT.......COLD....WARM (<<THIS IS THE HEADER)
2........CA
3........FL
4........TX

SHEET 2
.........A............B................C......... .
1.......STATE..CLIMATE....TOTAL CITIES (<<THIS IS THE HEADER)
2.........CA.......COLD.............5
3.........FL........HOT..............6
4.........TX........HOT.............15
5.........CA.......WARM............2
6.........CA.......HOT...............7
7.........FL.......COLD..............3
8.........TX.......WARM.............8

I need a formula where SHEET 1 is filled as follows:
.........A..........B..........C..........D
1.......STATE....HOT.....COLD....WARM (<<THIS IS THE HEADER)
2........CA.........7...........5.........2....
3........FL..........6...........3.........0...
4........TX.........15..........0.........8...