index with match

[neilpateluk]

Hi,

I require the ability to use the Index function with 2 Match functions... essentially what I require is to index a table and then for the row number field it will be dynamic, depending on both the name in one column and the date in another...

INDEX($A$7:$V$5000, Match(h7:h5000, b2,0), 10) (where b2 is matching the name)

But how can I also ask it to match the date so that it picks out only where the name is John Smith and date is between 01.04.10 and 07.04.10?? I know sumproduct can do this, but don't know if there is a way to return the row number of this?

[NBVC]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!