Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup by column & row
Is there a formula or VBA coding that will allow me to serch a range that has
a criteria for the row and another one by the column? For example I have 2/1 2/2 2/3 ATL 40 45 35 BOS 23 9 0 CVG 4 12 10 I need to search this range above and say I need the number for BOS on 2/2, how could I do this? Any help would be appreciated. thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup by column & row
see:
http://www.cpearson.com/excel/lookups.htm#DoubleLookup -- Gary''s Student gsnu200703 "Curt D." wrote: Is there a formula or VBA coding that will allow me to serch a range that has a criteria for the row and another one by the column? For example I have 2/1 2/2 2/3 ATL 40 45 35 BOS 23 9 0 CVG 4 12 10 I need to search this range above and say I need the number for BOS on 2/2, how could I do this? Any help would be appreciated. thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup by column & row
=INDEX(A1:D4,MATCH("BOS",A1:D1,0),MATCH(Datevalue( "2/2/06"),A1:D1,0))
(assuming 2/1, 2/2, 2/3 are real dates in 2006. Bob Umlas Excel MVP "Curt D." wrote in message ... Is there a formula or VBA coding that will allow me to serch a range that has a criteria for the row and another one by the column? For example I have 2/1 2/2 2/3 ATL 40 45 35 BOS 23 9 0 CVG 4 12 10 I need to search this range above and say I need the number for BOS on 2/2, how could I do this? Any help would be appreciated. thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup by column & row
You probably want to use a combination of Vlookup and Match to do that
=Vlookup("BOS", A2:D4, match("2/3", B1:B4, false) + 1, false) or something like that... -- HTH... Jim Thomlinson "Curt D." wrote: Is there a formula or VBA coding that will allow me to serch a range that has a criteria for the row and another one by the column? For example I have 2/1 2/2 2/3 ATL 40 45 35 BOS 23 9 0 CVG 4 12 10 I need to search this range above and say I need the number for BOS on 2/2, how could I do this? Any help would be appreciated. thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup by column & row
If I changed the Datevalue to the names of the week (Monday, Tuesday...)
would it just be MATCH("Tuesday",A1:A10,0)? or if I wanted it to look up a value in a different cell then it would be MATCH(A2,A1:A10,0),MATCH(B1,A1:A10,0) correct? "Bob Umlas" wrote: =INDEX(A1:D4,MATCH("BOS",A1:D1,0),MATCH(Datevalue( "2/2/06"),A1:D1,0)) (assuming 2/1, 2/2, 2/3 are real dates in 2006. Bob Umlas Excel MVP "Curt D." wrote in message ... Is there a formula or VBA coding that will allow me to serch a range that has a criteria for the row and another one by the column? For example I have 2/1 2/2 2/3 ATL 40 45 35 BOS 23 9 0 CVG 4 12 10 I need to search this range above and say I need the number for BOS on 2/2, how could I do this? Any help would be appreciated. thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup by column & row
Thanks everyone I'll give this a try later tonight. It looks promising.
This will make it a lot easier. "Curt D." wrote: Is there a formula or VBA coding that will allow me to serch a range that has a criteria for the row and another one by the column? For example I have 2/1 2/2 2/3 ATL 40 45 35 BOS 23 9 0 CVG 4 12 10 I need to search this range above and say I need the number for BOS on 2/2, how could I do this? Any help would be appreciated. thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup by column & row
Hi Curt D.
assuming that your data is arranged neatly with something like in A1:D4 2/1 2/2 2/3 ATL 40 45 35 BOS 23 9 0 CVG 4 12 10 try toolsoptioncalculation tabcheck Accept Labels in formulas then you can just type in any cell from the same sheet <only =BOS 2/2 after enter, the cell will display result as 9, the formula will APPEAR as =BOS 2/2/2007 this is a fast search for a single [row*col] match. without further regards, -- ***** birds of the same feather flock together.. "Curt D." wrote: Thanks everyone I'll give this a try later tonight. It looks promising. This will make it a lot easier. "Curt D." wrote: Is there a formula or VBA coding that will allow me to serch a range that has a criteria for the row and another one by the column? For example I have 2/1 2/2 2/3 ATL 40 45 35 BOS 23 9 0 CVG 4 12 10 I need to search this range above and say I need the number for BOS on 2/2, how could I do this? Any help would be appreciated. thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup looks to the prior column if zero appears in the lookup col | Excel Discussion (Misc queries) | |||
LookUp Function with Two Column Search Returning One Column Value | Excel Worksheet Functions | |||
macro column lookup enter data in another column same row | Excel Programming | |||
lookup text in one column, count in another column | Excel Worksheet Functions | |||
Calculating totals in a column based on a lookup in another column | Excel Worksheet Functions |