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

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

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

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

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

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

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