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

Anne Troy

MrExcel MVP
Joined
Feb 18, 2002
Messages
2,632
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.
 
Upvote 0
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
 
Upvote 0
Ah!

Feel free to view!

[file removed]

_________________
TheWordExpert
This message was edited by Dreamboat on 2002-04-30 17:07
 
Upvote 0
Jay, if you're at notes.... I'll email the real thing!
 
Upvote 0
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
 
Upvote 0
Thanks anyway, Aladin!
Jay P has my file...

You guys are the best!!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top