Match by using several criterias plus offset?

[Lingodawg]

Hello,

Please look at the following picture which is a basic layout of what Im after:

http://img.ihack.se/one/4239Excel.JPG

I want to enter city plus date and then I want a function that derives the price. I am thinking some kind of offset function where it first looks at the city to "lock a row" and then look for between what cells the date fits and then offsets one to the right? Maybe I am way off here, I really need some advise!

All help is appreciated!

[Ron Coderre]

It would be very helpful if you post some sample data and what values you want to see returned.

[Lingodawg]

It would be very helpful if you post some sample data and what values you want to see returned.

How do you mean? They are in the picture. I cant make any tables here. Basically in the picture the last column to the right is derived from D2 (New York, with a date between A2 and A3). I hope I get my point across

[Lingodawg]

Using an IF command, is it possible to start a new if command if the first one returns false? Like this:

100-120 cm: Dwarf
160-170 cm: Average Women
170-185 cm: Average man

Basically if I have a value, let's say 174 cm is it possible to do something like this:

If value is between 100-120 return dwarf, if not look if its in average women, if not look if its in average man?

[Ron Coderre]

I understand what you're trying to do...I missed it the first time looked.

With
A2: a city...eg New York
B2: a date...eg 2010-05-24

and the price schedule in this format in cells E1:H5

Please Login or Register  to view this content.

This formula returns the price associated with the city and the date:

Please Login or Register  to view this content.

In the above example, the formula returns: $1,500

Is that something you can work with?

[contaminated]

I take look at this
Using table in picture

[Lingodawg]

I take look at this
Using table in picture

I understand what you're trying to do...I missed it the first time looked.

With
A2: a city...eg New York
B2: a date...eg 2010-05-24

and the price schedule in this format in cells E1:H5

Please Login or Register  to view this content.

This formula returns the price associated with the city and the date:

Please Login or Register  to view this content.

In the above example, the formula returns: $1,500

Is that something you can work with?

Unfortunally I can not since the cities will be in different datespans. And I can't use , in code have to use ;

That's off-topic but anyone know why? Maybe because I have swedish version of excel?

[Lingodawg]

I take look at this
Using table in picture

Wow, exactly what I needed... I am not even gonna ask what's inside that formula

Only problem is that it works in Excel 2010 beta (because it is in english) but not in my excel 2003 since its swedish. Is there any add-on or function where I can copy a formula in english and it automatically translate it to work with swedish?

[contaminated]

It had to be translated automatically
BTW I fount an error in formula

Please Login or Register  to view this content.

[daddylonglegs]

That formula doesn't work for me - if I input a date in the 2nd date range the answer is a date....and if I enter a date which isn't in any of the date ranges I get a result....surely that should be an error?

As Ron implies, the layout makes this much more complex that it needs to be, will you only have 3 date ranges or is that just an example? In your example there are no gaps in the ranges, will that always be the case?

[Lingodawg]

That formula doesn't work for me - if I input a date in the 2nd date range the answer is a date....and if I enter a date which isn't in any of the date ranges I get a result....surely that should be an error?

As Ron implies, the layout makes this much more complex that it needs to be, will you only have 3 date ranges or is that just an example? In your example there are no gaps in the ranges, will that always be the case?

There will be gaps in ranges which is the major problem that makes this so frustrating

Im still wondering if I can make an "IF" statement which goes on to a new function when it returns false? Look at my example with the cm in some posts above...

[daddylonglegs]

If you can add a header row with "To", "From" etc. (see attached) then you can use this formula

=SUMPRODUCT((INDEX(B5:J8,MATCH(A2,A5:A8,0),0)<=B2)*(INDEX(C5:K8,MATCH(A2,A5:A8,0), 0)>=B2)*(B4:J4="To")*(C4:K4="From"),INDEX(D5:L8,MATCH(A2,A5:A8,0),0))

If the date isn't within one of the ranges listed for that location you'll get zero