MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Dual lookup or index or ?...


Posted by John Prins on October 09, 2001 6:24 AM

Following is a dataset, to which I am trying a insert formulas, using two variables. For example, in the cell now diplayed "#N/A", I am trying to lookup month 1 in the Month row AND year 1 in the Year row, and then to display the value found in the corresponding Value row. I was thinking it is a dual LOOKUP formula or an INDEX formula, but couldn't get it to work. Please help.

Year 1 1 1 2 2 2
Month 1 2 3 4 5 1


Value 100 200 300 200 600 800 200 1000 100 500

Year
1 2
Month
1 #N/A
2
3
4
5


Posted by IML on October 09, 2001 6:36 AM

You could use an array formula for this. I assumed your year you wanted was in B6, and month in A7. I assummed your year data (without labels was B1:G1, month B2:G2, amoun B3:G3)
=SUM(($B$1:$E$1=B$6)*($B$2:$E$2=$A7)*($B$3:$E$3))

when you paste this in, hit F2 and then control shift enter. You can copy it to other cells as you would any other formula.

good luck

Posted by IML on October 09, 2001 6:46 AM


Sorry, ranges for year were assumed B1:E1, month B2:E2, and amount B3:E3, the were right in the formula, not in my description.

I should also note
=SUMPRODUCT(($B$1:$E$1=B$6)*($B$2:$E$2=$A7)*($B$3:$E$3))
will work without the need to hit control shift enter.

Posted by John Prins on October 09, 2001 7:02 AM

Thank you IML. I am a relatively new Excel user. Your formula did work, however I don't understand the logic in the formula. Would you mind explaining the logic in the formula?

Posted by Eric on October 09, 2001 7:03 AM

Mr. IML, couple of noob questions

Sorry to ask so many questions, I really don't have a good handle on these array formulae yet.

Should the E's in your formula be replaced with G's

does the CSE sum equation do the same thing as:

=SUMPRODUCT(($B$2:$G$2=$A7)*($B$3:$G$3=$B7)*($B$4:$G$4))

given the difference in our designs with regard to where the year and month criteria are entered (I have them in A7 and B7 respectively)? : Following is a dataset, to which I am trying a insert formulas, using two variables. For example, in the cell now diplayed "#N/A", I am trying to lookup month 1 in the Month row AND year 1 in the Year row, and then to display the value found in the corresponding Value row. I was thinking it is a dual LOOKUP formula or an INDEX formula, but couldn't get it to work. Please help. : Year 1 1 1 2 2 2

Posted by Eric on October 09, 2001 7:08 AM

Woops, you answered most of these while I was typing (NT)!

Should the E's in your formula be replaced with G's does the CSE sum equation do the same thing as: =SUMPRODUCT(($B$2:$G$2=$A7)*($B$3:$G$3=$B7)*($B$4:$G$4)) given the difference in our designs with regard to where the year and month criteria are entered (I have them in A7 and B7 respectively)?

Posted by IML on October 09, 2001 7:26 AM

The jist of it comes down to if you type =true+true in a cell and hit enter. What the formula does is a logical comparison of each number in the range and assign a true or false value to it. Excel count True as 1 and false as zero.
So let say you have column A1:A3 with 1,2,3 in it. and B1:B3 with 4,5,6 in it and C1:C3 with 7,8,9 in it.
with your example you want let say you want to find the result in C where column A=3 and col B = 6.
The formula would be
=SUM((A1:C1=3)*(A2:C2=6)*(A3:C3)) and hit control shift enter.

Now go to the formula bar and highligh A1:C1=3 and hit f9. It will return False,False, True
Do the same for while highlighting A2:C2=6, to yield False, False , True.
Repeat for A3:C3 to yield {7,8,9}

Your formula now looks like
=SUM(({FALSE,FALSE,TRUE})*({FALSE,FALSE,TRUE})*({7,8,9}))

Now if you highlight from the second parenthesis (can't spell) in you get
=SUM({0,0,9})
The sum of 0,0, and 9 equals your answer. Aladin or Mark W. could provide a less sophomoric answer.