HLOOKUP + VLOOKUP + TODAY()

[adair]

Hello,

I'm trying to create a formula that would enable me to lookup both
horizontal + vertical (but with vertical it would be today).

For example;

aberdeen celtic rangers
23/11/2008 12000 52000 49000
24/11/2008 14000 45000 47000
25/11/2008 15000 65000 52000

I'd like to create a formula to would locate the text 'rangers' on row
A and then match the value in this column using TODAY() in column A.
So in this example, the formula would get 52000.

Thanks

 

[Bernard Liengme]

With the text in B1, C1, D1, dates in A2:A3; data in B24
date aberdeen celtic rangers
23/11/2008 12000 52000 49000
24/11/2008 14000 45000 47000
25/11/2008 15000 65000 52000

In G1 enter the team to find: rangers
In H1 locate its postion in the list of teams with =MATCH(G1,B11,FALSE) ;
this returns 3 since the team is in postion 3
In G2 use =TODAY() and in H2 use =MATCH(G2,A2:A4,FALSE), this returns 3
since today's date in in the third position
Locate the required data with =INDEX(B24,H2,H1) This returns the expected
52000

Combine all this into one formula as
=INDEX(B24,MATCH(TODAY(),A2:A4,FALSE),MATCH(G1,B11,FALSE))

best wsihes

 

[adair]

With the text in B1, C1, D1, dates in A2:A3; data in B24
date            aberdeen   celtic        rangers
23/11/2008 12000        52000        49000
24/11/2008 14000        45000        47000
25/11/2008 15000        65000        52000

In G1 enter the team to find: rangers
In H1 locate its postion in the list of teams with =MATCH(G1,B11,FALSE) ;
this returns 3 since the team is in postion 3
In G2 use =TODAY() and in H2 use =MATCH(G2,A2:A4,FALSE), this returns3
since today's date in in the third position
Locate the required data with =INDEX(B24,H2,H1)  This returns the expected
52000

Combine all this into one formula as
=INDEX(B24,MATCH(TODAY(),A2:A4,FALSE),MATCH(G1,B11,FALSE))

best wsihes

Thats great and works a treat! Thank you! I thought VLOOKUP & HLOOKUP
would be required for this type of thing...

 

[Shane Devenshire]

Hi,

There are many was to do this and one could use VLOOKUP and HLOOKUP along
with OFFSET, INDIRECT and others, here are two other short solutions:

=INDEX(B24,MATCH(TODAY(),A2:A4),MATCH(F1,B11))
or
=SUMPRODUCT((B11=F1)*(A2:A4=TODAY())*B24)
or
=SUM((B11=F1)*(A2:A4=TODAY())*B24)

This last one is array entered - press Shift+Ctrl+Enter to enter it.

If these help, please click the Yes button.

Cheers,
Shane Devenshire