Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Double Lookup
I have to lookup in two ways and don't know how. I need to lookup a certain
date that is always in B5 but that may change. and a certain name that is always in A5 but that may change. The list shows names and amounts assigned to the point where name and date meets and I need the amount. For example in cell B10 the amount is $5. Cell B5 shows 10-1-2009 and Cell A5 shows james. So I need to look up that james got $5 on the date 10-1-2009. The report is weekly so the pay period ending will change every two weeks. That is where my trouble is. Thanks for your help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Double Lookup
Debra Dalgleish has lots of notes:
http://www.contextures.com/xlFunctions03.html (for =index(match())) and http://contextures.com/xlFunctions02.html#Trouble Look at example #3. Teddy-B wrote: I have to lookup in two ways and don't know how. I need to lookup a certain date that is always in B5 but that may change. and a certain name that is always in A5 but that may change. The list shows names and amounts assigned to the point where name and date meets and I need the amount. For example in cell B10 the amount is $5. Cell B5 shows 10-1-2009 and Cell A5 shows james. So I need to look up that james got $5 on the date 10-1-2009. The report is weekly so the pay period ending will change every two weeks. That is where my trouble is. Thanks for your help! -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Double Lookup
you can use a SUMPRODUCT...
Sounds like A5 and B5 are entered by you, then you want to look up these values in, say, rows 6 through 100? =SUMPRODUCT((A6:A100=A5)*(B6:B100=B5)*C6:C100) would return the date that cell A5 and B5 match up with the table. This assumes you only have one instance of A5 and B5 in yoru table. If you can have the same person with the same date, you will get the sum of amounts on that date for that person... "Teddy-B" wrote: I have to lookup in two ways and don't know how. I need to lookup a certain date that is always in B5 but that may change. and a certain name that is always in A5 but that may change. The list shows names and amounts assigned to the point where name and date meets and I need the amount. For example in cell B10 the amount is $5. Cell B5 shows 10-1-2009 and Cell A5 shows james. So I need to look up that james got $5 on the date 10-1-2009. The report is weekly so the pay period ending will change every two weeks. That is where my trouble is. Thanks for your help! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Double Lookup
Thank for your time. In this casde I need to look at another sheet and find
the date (B3:B33) that matches B5 on the current sheet. When the date data matches, then it should be brought over. Would I do SUMPRODUCT, then LOOKUP or LOOKUP first, then SUMPRODUCT? Would this be a case for V or H lookup? "Sean Timmons" wrote: you can use a SUMPRODUCT... Sounds like A5 and B5 are entered by you, then you want to look up these values in, say, rows 6 through 100? =SUMPRODUCT((A6:A100=A5)*(B6:B100=B5)*C6:C100) would return the date that cell A5 and B5 match up with the table. This assumes you only have one instance of A5 and B5 in yoru table. If you can have the same person with the same date, you will get the sum of amounts on that date for that person... "Teddy-B" wrote: I have to lookup in two ways and don't know how. I need to lookup a certain date that is always in B5 but that may change. and a certain name that is always in A5 but that may change. The list shows names and amounts assigned to the point where name and date meets and I need the amount. For example in cell B10 the amount is $5. Cell B5 shows 10-1-2009 and Cell A5 shows james. So I need to look up that james got $5 on the date 10-1-2009. The report is weekly so the pay period ending will change every two weeks. That is where my trouble is. Thanks for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Double Lookup | Excel Worksheet Functions | |||
Variable Lookup/Double Lookup | Excel Worksheet Functions | |||
Double LOOKUP? Help please... | Excel Worksheet Functions | |||
double lookup | Excel Worksheet Functions | |||
Double lookup | Excel Worksheet Functions |