| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I am trying to lookup a cell based upon what is in row 3 and colum
I am trying to lookup a cell based upon what is in row 3 and column A. Lets
say in column A I have names, row 3 has a date. How do I get the cell that intersect with those 2 things? I have been playing with: =INDEX(C3:M29,MATCH($A$4,A:A,0))&(MATCH($C$3,3:3,0 )) but I can't figure this out..... Help. Thanx, David. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to lookup a cell based upon what is in row 3 and colum
Assume you want to lookup names in A3:A29 and dates in A3:M3 then use 2 cells that are not part of it other wise you obviously are going to get the wrong coordinates =INDEX(A3:M29,MATCH(D1,A3:A29,0),MATCH(E1,A3:M3,0) ) where D1 holds the name and E1 the date, meaning that is the name is found in A10 and the date in H3 then the value will be from H10 -- Regards, Peo Sjoblom "David A." wrote in message ... I am trying to lookup a cell based upon what is in row 3 and column A. Lets say in column A I have names, row 3 has a date. How do I get the cell that intersect with those 2 things? I have been playing with: =INDEX(C3:M29,MATCH($A$4,A:A,0))&(MATCH($C$3,3:3,0 )) but I can't figure this out..... Help. Thanx, David. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to lookup a cell based upon what is in row 3 and c
You are a lifesaver.....Thanx.
"Peo Sjoblom" wrote: Assume you want to lookup names in A3:A29 and dates in A3:M3 then use 2 cells that are not part of it other wise you obviously are going to get the wrong coordinates =INDEX(A3:M29,MATCH(D1,A3:A29,0),MATCH(E1,A3:M3,0) ) where D1 holds the name and E1 the date, meaning that is the name is found in A10 and the date in H3 then the value will be from H10 -- Regards, Peo Sjoblom "David A." wrote in message ... I am trying to lookup a cell based upon what is in row 3 and column A. Lets say in column A I have names, row 3 has a date. How do I get the cell that intersect with those 2 things? I have been playing with: =INDEX(C3:M29,MATCH($A$4,A:A,0))&(MATCH($C$3,3:3,0 )) but I can't figure this out..... Help. Thanx, David. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to lookup a cell based upon what is in row 3 and c
Ok, This didn't work.
=INDEX('Daily Productivity'!A3:M29,MATCH("""A4""",'Daily Productivity'!A:A,0),MATCH("""C3""",'Daily Productivity'!3:3,0)) The """A4""" and """C3""" are on the sheet I want this information to be "Sheet1. And no I don't have the """"" in the formula. I geet the "N/A" error......What should I do? "Peo Sjoblom" wrote: Assume you want to lookup names in A3:A29 and dates in A3:M3 then use 2 cells that are not part of it other wise you obviously are going to get the wrong coordinates =INDEX(A3:M29,MATCH(D1,A3:A29,0),MATCH(E1,A3:M3,0) ) where D1 holds the name and E1 the date, meaning that is the name is found in A10 and the date in H3 then the value will be from H10 -- Regards, Peo Sjoblom "David A." wrote in message ... I am trying to lookup a cell based upon what is in row 3 and column A. Lets say in column A I have names, row 3 has a date. How do I get the cell that intersect with those 2 things? I have been playing with: =INDEX(C3:M29,MATCH($A$4,A:A,0))&(MATCH($C$3,3:3,0 )) but I can't figure this out..... Help. Thanx, David. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to lookup a cell based upon what is in row 3 and c
Are A4 and C3 addresses of cells or really text that you want to use?
If they're really cell addresses, then drop all those double quotes: =INDEX('Daily Productivity'!A3:M29,MATCH(A4,'Daily...... David A. wrote: Ok, This didn't work. =INDEX('Daily Productivity'!A3:M29,MATCH("""A4""",'Daily Productivity'!A:A,0),MATCH("""C3""",'Daily Productivity'!3:3,0)) The """A4""" and """C3""" are on the sheet I want this information to be "Sheet1. And no I don't have the """"" in the formula. I geet the "N/A" error......What should I do? "Peo Sjoblom" wrote: Assume you want to lookup names in A3:A29 and dates in A3:M3 then use 2 cells that are not part of it other wise you obviously are going to get the wrong coordinates =INDEX(A3:M29,MATCH(D1,A3:A29,0),MATCH(E1,A3:M3,0) ) where D1 holds the name and E1 the date, meaning that is the name is found in A10 and the date in H3 then the value will be from H10 -- Regards, Peo Sjoblom "David A." wrote in message ... I am trying to lookup a cell based upon what is in row 3 and column A. Lets say in column A I have names, row 3 has a date. How do I get the cell that intersect with those 2 things? I have been playing with: =INDEX(C3:M29,MATCH($A$4,A:A,0))&(MATCH($C$3,3:3,0 )) but I can't figure this out..... Help. Thanx, David. -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to lookup a cell based upon what is in row 3 and c
I'm not using the quotes in the formula.
This is what I'm using but it doesn't locate the dates to make sure I'm pulling the correct information for each rep. =IF(ISERROR(TEXT(SUM(INDEX('Daily Productivity'!D:D,MATCH($A$4,'Daily Productivity'!$A:$A,0))*60*60+INDEX('Online Productivity'!E:E,MATCH($A$4,'Online Productivity'!$A:$A,0)+0)*INDEX('Online Productivity'!E:E,MATCH($A$4,'Online Productivity'!$A:$A,0)+2)+INDEX('Online Productivity'!E:E,MATCH($A$4,'Online Productivity'!$A:$A,0)+3)/60/60)/60/60/24,"H:mm")),"0",(TEXT(SUM(INDEX('Daily Productivity'!D:D,MATCH($A$4,'Daily Productivity'!$A:$A,0))*60*60+INDEX('Online Productivity'!E:E,MATCH($A$4,'Online Productivity'!$A:$A,0)+0)*INDEX('Online Productivity'!E:E,MATCH($A$4,'Online Productivity'!$A:$A,0)+2)+INDEX('Online Productivity'!E:E,MATCH($A$4,'Online Productivity'!$A:$A,0)+3)/60/60)/60/60/24,"H:mm"))) It gets the numbers but I don't know if its for the right date. "Dave Peterson" wrote: Are A4 and C3 addresses of cells or really text that you want to use? If they're really cell addresses, then drop all those double quotes: =INDEX('Daily Productivity'!A3:M29,MATCH(A4,'Daily...... David A. wrote: Ok, This didn't work. =INDEX('Daily Productivity'!A3:M29,MATCH("""A4""",'Daily Productivity'!A:A,0),MATCH("""C3""",'Daily Productivity'!3:3,0)) The """A4""" and """C3""" are on the sheet I want this information to be "Sheet1. And no I don't have the """"" in the formula. I geet the "N/A" error......What should I do? "Peo Sjoblom" wrote: Assume you want to lookup names in A3:A29 and dates in A3:M3 then use 2 cells that are not part of it other wise you obviously are going to get the wrong coordinates =INDEX(A3:M29,MATCH(D1,A3:A29,0),MATCH(E1,A3:M3,0) ) where D1 holds the name and E1 the date, meaning that is the name is found in A10 and the date in H3 then the value will be from H10 -- Regards, Peo Sjoblom "David A." wrote in message ... I am trying to lookup a cell based upon what is in row 3 and column A. Lets say in column A I have names, row 3 has a date. How do I get the cell that intersect with those 2 things? I have been playing with: =INDEX(C3:M29,MATCH($A$4,A:A,0))&(MATCH($C$3,3:3,0 )) but I can't figure this out..... Help. Thanx, David. -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I am trying to lookup a cell based upon what is in row 3 and c
When I've verifying a formula like this, I'll use some cells with just smaller
pieces of the formula. I'd drop the =if(iserror(... and all the stuff through "0". I'd drop the =text() and =sum() and just put those individual components into their own cells. David A. wrote: I'm not using the quotes in the formula. This is what I'm using but it doesn't locate the dates to make sure I'm pulling the correct information for each rep. =IF(ISERROR(TEXT(SUM(INDEX('Daily Productivity'!D:D,MATCH($A$4,'Daily Productivity'!$A:$A,0))*60*60+INDEX('Online Productivity'!E:E,MATCH($A$4,'Online Productivity'!$A:$A,0)+0)*INDEX('Online Productivity'!E:E,MATCH($A$4,'Online Productivity'!$A:$A,0)+2)+INDEX('Online Productivity'!E:E,MATCH($A$4,'Online Productivity'!$A:$A,0)+3)/60/60)/60/60/24,"H:mm")),"0",(TEXT(SUM(INDEX('Daily Productivity'!D:D,MATCH($A$4,'Daily Productivity'!$A:$A,0))*60*60+INDEX('Online Productivity'!E:E,MATCH($A$4,'Online Productivity'!$A:$A,0)+0)*INDEX('Online Productivity'!E:E,MATCH($A$4,'Online Productivity'!$A:$A,0)+2)+INDEX('Online Productivity'!E:E,MATCH($A$4,'Online Productivity'!$A:$A,0)+3)/60/60)/60/60/24,"H:mm"))) It gets the numbers but I don't know if its for the right date. "Dave Peterson" wrote: Are A4 and C3 addresses of cells or really text that you want to use? If they're really cell addresses, then drop all those double quotes: =INDEX('Daily Productivity'!A3:M29,MATCH(A4,'Daily...... David A. wrote: Ok, This didn't work. =INDEX('Daily Productivity'!A3:M29,MATCH("""A4""",'Daily Productivity'!A:A,0),MATCH("""C3""",'Daily Productivity'!3:3,0)) The """A4""" and """C3""" are on the sheet I want this information to be "Sheet1. And no I don't have the """"" in the formula. I geet the "N/A" error......What should I do? "Peo Sjoblom" wrote: Assume you want to lookup names in A3:A29 and dates in A3:M3 then use 2 cells that are not part of it other wise you obviously are going to get the wrong coordinates =INDEX(A3:M29,MATCH(D1,A3:A29,0),MATCH(E1,A3:M3,0) ) where D1 holds the name and E1 the date, meaning that is the name is found in A10 and the date in H3 then the value will be from H10 -- Regards, Peo Sjoblom "David A." wrote in message ... I am trying to lookup a cell based upon what is in row 3 and column A. Lets say in column A I have names, row 3 has a date. How do I get the cell that intersect with those 2 things? I have been playing with: =INDEX(C3:M29,MATCH($A$4,A:A,0))&(MATCH($C$3,3:3,0 )) but I can't figure this out..... Help. Thanx, David. -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup tab based on cell reference | Excel Discussion (Misc queries) | |||
| vlookup- is a must that lookup value should be the leftmost colum? | Excel Discussion (Misc queries) | |||
| How to lookup row # based on content of another column's cell | Excel Worksheet Functions | |||
| Formula to lookup last number in colum | Excel Worksheet Functions | |||
| lookup in colum a and compare values in colum b | Excel Worksheet Functions | |||
Linear Mode
