COUNTIFS with VLOOKUP

[slinky]

Perhaps a misleading title, but here's the formula I want to use...

=COUNTIFS(APM!B:B,Main_Sheet!B6,VLOOKUP(APM!A:A,INFO!A:B,2,0),">="&I1-31)

(ignore the fact that it generates an error, you should get the gist of what I'm attempting to do from the layout of it)..

The issue is, of course, that VLOOKUP will only return the first result that it finds, this is an issue as I'm attempting to carry out that lookup on a table that contains approx 1000 rows..

This is all in the name of simplifying an import process to remove the requirement to generate VLOOKUPs for date information from the INFO tab..

Info Contains the following :

A B C
1 ID DATE SERVER
2 1 01/08/2010 FISH
3 2 01/08/2010 FISH


APM Contains the following
A B
1 ID VERSION
2 1 5.00
3 2 4.00


Main_Sheet contains the formula I'm attempting to use..
I1 =TODAY()
B6 = 5.00

brain hurts.. I'm sure I should be using something along the lines of Index/Match/Offset to get what I need, but I'll be damned if I can fathom how to use them today!

Shout if you need any more info..

TIA..

 

[Andrew Poulsom]

I've tried several things, but I haven't managed to return an array of the relevant rows in Info. It may be possible, but I don't know how. Sorry.

 

[slinky]

:weep:

Thanks for trying Andrew..

 

[slinky]

Just as a quick update, I've admitted defeat and written a long and dirty macro to do the data import and generate all of the VLOOKUP queries..

If anyone does find a way of doing what I was after, I'd be interested to hear what you have to say..

TTFN..