Lookup help

[jlg5454]

I need a formula to find a number based off of crosstab query from access. The value needs to look at the date and column heading. example:

AAA bbb ccc dddd
2/1 5 8 7 9
2/2 7 4 7 3
2/3 1 1 7 0
2/4 2 7 8 9

This data above is generated from an access crosstab query and placed The column heading can change from day to day. For instance eeee may be added two days from now when it recieves data. I tried several formulas and match/index formulas such as this one INDEX(H43:H46,MATCH(A7,H43:H46,),MATCH(H6,H43:M43)) which do not work. Thanks for any assistance possible.

 

[KenWright]

You've given no data regarding what is where, but on the assumption that A7 is a match for one of the dates, and contains a date identical to one in the list, and H6 (sure you didn't mean A6??), is a match for one of the column headings, then try the following edit of your formula:-

INDEX(H43:M46,MATCH(A7,H43:H46,0),MATCH(H6,H43:M43,0))

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[jlg5454]

Thanks,
Works great except I need to put a zero if there is no match. Is there an iserror for index?

 

[KenWright]

=IF(ISNA(INDEX(H43:M46,MATCH(A7,H43:H46,0),MATCH(H6,H43:M43,0))),0,INDEX(H43:M46,MATCH(A7,H43:H46,0),MATCH(H6,H43:M43,0)))

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[jlg5454]

Works great! Thanks for all your help.

 

[KenWright]

You're welcome

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------