Searching on two criteria

[jdub765]

Wow, I wasn't sure if I'd be able to find anyone able to help me out, and I was surprised to find this forum!

Here's my problem... I have two search criteria that I need to search on, State, and Date.

I have a separate sheet called "State" that has the table I need to search.
The sheet has the columns State, Date, ContractNum, and is ordered alphabetically by State, then by Date.

When I enter a State, and then a Date, I'd like it to find the state, then round down to the lower date, and then return the corresponding ContractNum.

I've been searching the web for different functions I can use, but I can't seem to figure out a combination of functions that will work for me.
Thanks for any help you can provide!

[Bryan Hessey]

Wow, I wasn't sure if I'd be able to find anyone able to help me out, and I was surprised to find this forum!

Here's my problem... I have two search criteria that I need to search on, State, and Date.

I have a separate sheet called "State" that has the table I need to search.
The sheet has the columns State, Date, ContractNum, and is ordered alphabetically by State, then by Date.

When I enter a State, and then a Date, I'd like it to find the state, then round down to the lower date, and then return the corresponding ContractNum.

I've been searching the web for different functions I can use, but I can't seem to figure out a combination of functions that will work for me.
Thanks for any help you can provide!

Hi,

The easy way would be to insert a new column C on your lookup sheet, with =A1&B1 formula filled down the column.

Your lookup can then be

=If(Vlookup(A2,Sheet2!A$2:A$9999,1,False)<>A2,"",VLookup(A2&B2,Sheet2!C$2:D$9999,2,True))
(untested) or something similar.

Does this help you?
---

[jdub765]

I can't seem to get it to work correctly. It works okay if there aren't any other states, but once I include another state in the search, it just chooses the last date for the state Thanks for the help though!

[duane]

"The sheet has the columns State, Date, ContractNum, and is ordered alphabetically by State, then by Date"

"When I enter a State, and then a Date, I'd like it to find the state, then round down to the lower date, and then return the corresponding ContractNum"

if you just do a vlookup on the state won't it give you the contract number on the first entry of that state (and hence the lowest date)? Is that not what you desire? Or do you want the latest date for the state?

[jdub765]

I want to look for a certain contract number based on the date of service and state.

Like, if the service was performed in CA on 5/01/2002,
A...B...........C
AL 1/1/2006 342
CA 1/1/2002 456
CA 1/1/2003 567
It should return "456".

I figured out a formula that works...
=INDEX(C2:C1000, MATCH(DateValue(K2),IF(A2:A1000=(J2),B2:C1000),1),1)
Where K2 and J2 are my search values.

I simplified the formula so it's more readable, but that's the gist of it.

Thanks for your help guys! I'll probably be back soon with more questions