Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

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

  1. #1
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    ~Anne Troy

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ah!

    Feel free to view!

    [file removed]

    _________________
    TheWordExpert

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

  5. #5
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #6
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Dreamboat,

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

    Jay

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

  8. #8
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    You guys are the best!!
    ~Anne Troy

  9. #9
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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