1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unable to solve this question

Discussion in 'Ask an Excel Question' started by Abhijeet, May 10, 2017.

  1. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    Hi

    Below is question from that i am not resolve Ratings question rest i know how to do this

    Can you please tell me for this question(In Column J, show the ratings to all the customers on the basis of billing amount. Refer to the conditions mentioned in the range A1:C11 on reference sheet)

    In Column I give the ranking to all the customers i.e. Rank 1 to the highest billing , Rank 2 to the second highest billing and so on
    In Column J, show the ratings to all the customers on the basis of billing amount. Refer to the conditions mentioned in the range A1:C11 on reference sheet
    In Cell N1, display a drop down showing all the months i.e January, February till December
    In Cell N2, display a drop down showing three products i.e. Gold, Silver and Diamond
    In Cell N3, apply a function which should search the values of N1 and N2 in the range A17:E30 on reference sheet and show the corresponding Sales

    Attached Files:

  2. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    371
    Hi @Abhijeet!

    You can use this formulae:
    =INDEX(Reference!C$2:C$11,MATCH(H2,Reference!B$2:B$11,-1))

    Blessings!
    Thomas Kuriakose likes this.
  3. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi,

    Something like this?
    (attached)

    Attached Files:

    Thomas Kuriakose likes this.
  4. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    Actually i tried Index & match but unable to get result & Vlookup also this is not work but when sort this data then work
    Can you please tell me why need sort data for vlookup getting result
  5. vletm

    vletm Well-Known Member

    Messages:
    2,805
    Abhijeet - Can you please tell me why need sort data for vlookup getting result
    Sometimes You would (re)read syntaxes:
    If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not have to be sorted.
  6. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    in this case then vlookup work or not if work then please
    tell the formula
  7. vletm

    vletm Well-Known Member

    Messages:
    2,805
    Abhijeet - vlookup works !
    You and many more just have to do it this way!
    Screen Shot 2017-05-10 at 19.25.23.png
    ReRead sometimes syntax and test Yourself.
  8. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    ok i am using 1 thats why its getting wrong
  9. vletm

    vletm Well-Known Member

    Messages:
    2,805
    Abhijeet
    It's not 'getting wrong'!
    It's working so!
  10. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    Vlookup not work in this file can you please upload same file with vlookup
  11. vletm

    vletm Well-Known Member

    Messages:
    2,805
    Abhijeet
    What do not work?
    Upload Your 'not work version in this file' and
    after that someone could see what have You done ... or not.
  12. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    In this file i use vlookup but not work please tell me

    Attached Files:

  13. Pavan Kumar Sistla

    Pavan Kumar Sistla Member

    Messages:
    45
    Hello Abhijeet,

    The VLOOKUP formula works and the reverse order when you are trying to do appropriate match.

    Here is the updated file.

    However, try to understand and recreate the file for your benefit and knowledge.

    Learn Index & Match combination:

    Index function refers to Table array, row no., column no. which retrieves the intersected value. For example: In a table of two columns (X, Y) and three rows (A, B, C), Index function considers the table array of 2 columns and 3 rows and accordingly give you the intersected result. In 2nd column, 3rd row, the value is retrieved. In your query, we have used MATCH function to retrieve the match number in both column (Gold, Silver etc.) and row (Jan, Feb etc.) and accordingly the sales are retrieved.

    Thank You and Regards,
    Pavan.

    Attached Files:

  14. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    What you are doing same like data sort then i know vlookup is work without data sort tell me how vlookup work this is my question i know when data is sort then vlookup work

    Veltm please tell me on this
  15. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,677
    You'll need key field to be sorted when using approximate match VLOOKUP or result will be inconclusive.

    Personally I'd use LOOKUP function with Reference table sorted smallest to largest.

    upload_2017-5-11_8-51-48.png

    upload_2017-5-11_8-52-30.png
  16. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    Yes means i am correct with out sorted data vlookup not work in approximate match case
  17. vletm

    vletm Well-Known Member

    Messages:
    2,805
    Abhijeet
    I gave You clear sample how to do it #7 Reply
    and You wrote 'ok i am using 1 thats why its getting wrong'
    After that You wrote that
    Vlookup not work in this file can you please upload same file with vlookup
    You skipped my reply and ... of course it won't work!
    Screen Shot 2017-05-11 at 18.28.58.png
    Even Excel has rules, how do it work!
    This 'Your case' is as same as
    1+2*3 = 7, but You want 1+2*3 = 9
  18. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    vletm i ask where lookup array table not sorted then apporox match not work
  19. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    what u show me in the pic exact match case then vlookup work but my case is not this
  20. vletm

    vletm Well-Known Member

    Messages:
    2,805
    Abhijeet as I wrote
    Even Excel has rules, how do it work!
    This 'Your case' is as same as
    1+2*3 = 7, but You want 1+2*3 = 9
    >
    I see - maybe You cannot see difference of those two tables - okay for me!
    >
    'ok i am using 1 thats why its getting wrong' - Yes!
    Have a nice day.
  21. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    i know that when i ask u in vlookup data need to sort or not u told no need u give example for exact match where i ask approx match
  22. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,677
    @Abhijeet

    Look at vletm's response in #7. As per formula syntax. He's using approximate match and sorted column A for Reference table. Pretty much same thing I did.
  23. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    Ok when you are telling me vlookup work without sorting array table then please upload file with formula so i know what is wrong when i used vlookup

    This Formula work in this type
    =INDEX(Reference!C$2:C$11,MATCH(H2,Reference!B$2:B$11,-1))

    Please upload file with vlookup without sort array table
  24. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    what showed in #7 post that array table is sorted please look my file in that array table is not sorted
  25. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,677
    Take a careful look... it's the lookup table and not the data table that's sorted.

Share This Page