Thanks:  0
Likes:  0

# Thread: Index, Match, it's all Greek to me!

1. 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.

2. 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.

3. 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 ]

4. Ah!

Feel free to view!

[file removed]

_________________
TheWordExpert

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

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

6. Hi Dreamboat,

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

Jay

7. 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?

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

You guys are the best!!

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

10. On 2002-04-30 17:46, Jay Petrulis wrote:

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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•