Index, Match, it's all Greek to me!

[Anne Troy]

I finally got this to work:

=IF(ISBLANK(D6),"",INDEX(hotelprice,MATCH(D4,hotel,0),MATCH(F2,seasonnumber,0)))

Now, here's what I need.

I need to add that if the value of D6 is greater than 1, I want to add *3* to the value in D4 and THAT's the column I want to return. In other words, D4 currently says 3, because that's the heading I want it to find the value (price of a night's stay) under. Now, have headings 1 through 6. 1 through 3 represent 3 variations (seasons) of the price the 1st night's stay. 4 through 6 represent the same 3 variations of the price of the 2nd night's stay.

I'm going to attempt to post my data momentarily.

[Anne Troy]

Hotel 1 2 3 4 5 6
ABC 55.00 71.25 73.75 15.00 18.75 18.75
BCD 80.00 92.50 98.75 68.75 68.75 81.25
CDE 46.25 61.25 61.25 25.00 27.50 31.25
DEF 55.00 67.50 71.25 25.00 25.00 27.50
EFG 111.25 123.75 130.00 81.25 81.25 87.50
FGH 80.00 92.50 98.75 31.25 33.75 33.75
GHI 65.00 73.75 77.50 50.00 50.00 56.25
HIJ 61.25 77.50 77.50 31.25 33.75 33.75
IJK 67.50 80.00 86.25 37.50 37.50 43.75
JKL 86.25 98.75 105.00 50.00 50.00 56.25
KLM 98.75 111.25 123.75 56.25 56.25 62.50


Praying before I hit submit.

[Jay Petrulis]

Hi,

Would either of these work?

=IF(ISBLANK(D6),"",INDEX(hotelprice,MATCH(IF(D6>1,D4+3,D4),hotel,0),MATCH(F2,seasonnumber,0)))

or

=IF(ISBLANK(D6),"",INDEX(hotelprice,MATCH(D4,hotel,0)+IF(D6>1,3,0),MATCH(F2,seasonnumber,0)))

My guess is the second one, but can't be sure until you post the data.

Bye,
Jay

Since you want to shift the columns, the added IF statements should go to the column area of the INDEX.

Where does your table start? What are the named ranges?


[ This Message was edited by: Jay Petrulis on 2002-04-30 16:56 ]

[Anne Troy]

Ah!

Feel free to view!

[file removed]

_________________
TheWordExpert

[ This Message was edited by: Dreamboat on 2002-04-30 17:07 ]

[Anne Troy]

Jay, if you're at notes.... I'll email the real thing!

[Jay Petrulis]

Hi Dreamboat,

Please send it. I have your table, but the ranges don't make sense to me.

Jay

[Aladin Akyurek]

On 2002-04-30 16:48, Dreamboat wrote:
Hotel 1 2 3 4 5 6
ABC 55.00 71.25 73.75 15.00 18.75 18.75
BCD 80.00 92.50 98.75 68.75 68.75 81.25
CDE 46.25 61.25 61.25 25.00 27.50 31.25
DEF 55.00 67.50 71.25 25.00 25.00 27.50
EFG 111.25 123.75 130.00 81.25 81.25 87.50
FGH 80.00 92.50 98.75 31.25 33.75 33.75
GHI 65.00 73.75 77.50 50.00 50.00 56.25
HIJ 61.25 77.50 77.50 31.25 33.75 33.75
IJK 67.50 80.00 86.25 37.50 37.50 43.75
JKL 86.25 98.75 105.00 50.00 50.00 56.25
KLM 98.75 111.25 123.75 56.25 56.25 62.50


Praying before I hit submit.

I understand this kind of Greek I hope , but the real Greek, alas.

What is in F2 -- a seasonnumber?

What is in D4 -- a hotel?

How do you want to indicate whether it's 1 night or 2?

Aladin

[Anne Troy]

Thanks anyway, Aladin!
Jay P has my file...

You guys are the best!!

[Jay Petrulis]

Hi Aladin,

The range names reference two sheets. The answer Dreamboat needed was the following:

=IF(ISBLANK(D6),"",INDEX(hotelprice,MATCH(D4,hotel,0),MATCH(F2,seasonnumber,0)+IF(D6>1,3,0)))

BTW, you are a machine! It is way past midnight in the Netherlands and you are still here. Is sleep your enemy?

You, on this board, and Peo S. on the other board are amazing.

Bye,
Jay

[Aladin Akyurek]

On 2002-04-30 17:46, Jay Petrulis wrote:
Hi Aladin,

The range names reference two sheets. The answer Dreamboat needed was the following:

=IF(ISBLANK(D6),"",INDEX(hotelprice,MATCH(D4,hotel,0),MATCH(F2,seasonnumber,0)+IF(D6>1,3,0)))

BTW, you are a machine! It is way past midnight in the Netherlands and you are still here. Is sleep your enemy?

You, on this board, and Peo S. on the other board are amazing.

Bye,
Jay

Thanks Jay. Rescued. I was going to give Dreamboat more Greek.

I didn't realize it was late.



Aladin