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

  • 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

Abhijeet

Active Member
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
 

Attachments

  • Test.xlsx
    13.8 KB · Views: 5
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
 
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.
 
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.
in this case then vlookup work or not if work then please
tell the formula
 
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.
 

Attachments

  • Test_Solved.xlsx
    16.2 KB · Views: 5
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
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.
 
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
 
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
 
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!
View attachment 41601
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
vletm i ask where lookup array table not sorted then apporox match not work
 
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.
 
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
 
@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.
 
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
 
Back
Top