HLOOKUP + VLOOKUP + TODAY()

  • Thread starter Thread starter adair
  • Start date Start date
A

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
 
With the text in B1, C1, D1, dates in A2:A3; data in B2:D4
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,B1:D1,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(B2:D4,H2,H1) This returns the expected
52000

Combine all this into one formula as
=INDEX(B2:D4,MATCH(TODAY(),A2:A4,FALSE),MATCH(G1,B1:D1,FALSE))

best wsihes
 
With the text in B1, C1, D1, dates in A2:A3; data in B2:D4
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,B1:D1,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(B2:D4,H2,H1)  This returns the expected
52000

Combine all this into one formula as
=INDEX(B2:D4,MATCH(TODAY(),A2:A4,FALSE),MATCH(G1,B1:D1,FALSE))

best wsihes
Click to expand...

Thats great and works a treat! Thank you! I thought VLOOKUP & HLOOKUP
would be required for this type of thing...
 
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(B2:D4,MATCH(TODAY(),A2:A4),MATCH(F1,B1:D1))
or
=SUMPRODUCT((B1:D1=F1)*(A2:A4=TODAY())*B2:D4)
or
=SUM((B1:D1=F1)*(A2:A4=TODAY())*B2:D4)

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

If these help, please click the Yes button.

Cheers,
Shane Devenshire
 
Back
Top