vlookup with unique multiple criteria

[rodonnell]

Im just going to start with the tables that will help show what i am trying to do...

TABLE 1
File # Date id
2 15 ?
2 11 ?
2 7 ?
2 6 ?

TABLE 2
File # Start Stop id
2 9 13 1
2 14 16 2
2 1 8 3

Ok so say i have these two tables...i want to be able to look at the file # of line 1 in table 1, go to table 2 and return the id where the date from table 1 is between the start and stop in table 2. ie...in that example it should return id "2"

Ive been playing with vlookup to do this, but have gotten no where because it just takes the first match it finds. Any help will be greatly appreciated

thnx in advance

-reilly

[martindwilson]

attach a workbook with it laid out as you want showing the result you'd expect please

[NBVC]

See attached:

Formula used is:

=SUMPRODUCT(--($F$2:$F$4=A2),--($G$2:$G$4<=B2),--($H$2:$H$4>=B2),$I$2:$I$4)

copied down

[rodonnell]

thanks for the response.

that formula is exactly what i want to achieve, but it doesn't work for me because my id's are actually an combination of letters and numbers so it returns 0's everytime for me.

I have attached a spreadsheet that should give an idea of my issue

Thanks again

Reilly

[NBVC]

You'll have to change formula then... that only works if the ID's were numbers...

Try:

Please Login or Register  to view this content.

which you must confirm with CTRL+SHIFT+ENTER not just ENTER. Then copy it down the column.

See attached.

[rodonnell]

wow thnx for the quick response, i think that formula just might do it

thnx again,

Reilly

[NBVC]

Great!

Can you please mark your thread as Solved?

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save