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

vLookup based on 2 criteria

S

Serene

Member
I have 2 tables that have the following fields


Table 1)

StaffID RankID Score Quantum

Mary 1 75 (to be computed)


Table 2)

<Parameter ><Rank 1><Rank 2><Rank 3><Rank 4>

0-49 5 <0.00> <0.00> <0.00><0.00><0.00

50 4 <0.40> <0.40> <0.40><0.40><0.40

51-69 3 <1.30> <1.15> <1.05><1.00><0.95

70-89 2 <1.68> <1.44> <1.31><1.20><1.14

90-100 1 <2.10> <1.75> <1.60><1.42><1.33</p>

<p>I need to compute the quantum in Table 1 by looking up in Table 2.

This is to be based on (1) rank (2) Score

What is the most efficient way to construct the vlookup.

Should I change Table 2? (table 1 is fixed and I cannot modify it)</p>

<p>Sorry for not checking on earlier posts which may have the answers.


Sorry, I use < and > to assist with viewing the data since the spacing did not appear correct.

Thanks advance for your help!`
 
Hi ,


Can you please clarify ? In your Table 1 , Mary's rating ( "Quantum" ) is to be worked out based on her RankID ( which is 1 ) , and her Score ( which is 75 ).


In Table 2 , you have 5 values and labels which are enclosed within angle brackets "<" and ">" ; in addition , you also have values 0-49 , 5 , 50 , 4 , 51-69 , 3 , 70-89 , 2 , 90-100 and 1 ; what are these , and what role do they play in calculating a person's "quantum" ?


Can you take a specific example , say the RankID is 3 , and the score is 67 ; what would be the "quantum" and how would you derive it ?


Narayan
 
Please post a spreadsheet...


http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting
 
Dear Narayan,

If Mary is Rank 3 and score is 67, we should look up the row with the value between 51-69, in column Rank 3, the quantum given should be 1.00.


Thanks for the patience. Looking forward to you inputs
 
Hi ,


Thanks. So , in general , the score is looked up in the table of values - 0-49 , 50 , 51-69 , 70-89 , 90-100 to get a slot ; then within this slot , the rank decides which of the values <Rank 1> through <Rank 5> is returned as the quantum.


We need to assume a few points :


1. The table of values 0-49 , 50 , 51-69 , 70-89 , 90-100 is in column A , from cells A5 through A9.


2. The headers <Rank 1> through <Rank 5> are in columns B through F , in cells B4 , C4 , .... , F4.


3. The values 0.00 through 1.33 are entered in the table B5 through F9 , 25 values in all.


4. The data for any person , such as the name , RankID and Score will be in cells A2 , B2 and C2.


5. The formula will be entered in D2 ; it will make use of the values in cells B2 , C2 , and the table of values B5 through F9.


Is this OK with you ?


Narayan
 
Hi Narayan,


My excel is not working in my home PC right now, so I will describe it as:


1. The table of values 0-49 , 50 , 51-69 , 70-89 , 90-100 is in column A , from cells A10 through A15.


2. The headers <Rank 1> through <Rank 5> are in columns B through F , in cells B9 , C9 , .... , F9.


3. The values 0.00 through 1.33 are entered in the table B10 through F15 , 25 values in all.


4. The header for any person , such as the name , RankID and Score and quantum will be in cells A1 , b1 and C1 and D1. The actual data will be populated in rows from A2 through D8


5. The formula will be entered in D2 through D8 ; it will make use of the values in cells described in points 1-3 above.


If it is still unclear I will try to post the sheet tomorrow. (need to figure out how to share though)


Thanks again!
 
Hi Serene,


In my Humble view, things can be really simple if you split your Table of values into two columns. I tired Monte Calro Simulation once and found it technique simple and helpful!! So i suggest you that if :


- A1:D4 Contain the Headers and the output result.

- A10:B14 Contain the "Table of Values"-Split into two columns:

- C10:H14 Contain the Entries for Quantum & Rank.


The following formula in D4 will serve the purpose VLOOKUP(C2,A10:H14,B2+3)


A sample file is attached (Excel 2007 Version):

http://www.2shared.com/file/3Kn853g-/Serene_Sample.html


Regards,

FASEEH
 
Thanks Faseeh for your reply and sample worksheet.

To clarify, I wanted the formula to be able to lookup the correct quantum according to the rank. In your formula, "B2+3" is hardcoded to lookup the 3rd column which belongs to Rank 1. But how should I modify this formula to enable automatic lookup for the value according to the Rank, which means adding a second criteria to the vlookup.

Your inputs are greatly appreciated.


Best regards

Serene
 
Hi Serene ,


Can you check out the following worksheet ?


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21147


Please do not click on the hyperlink ; copy and paste the entire address in your browser.


Narayan
 
Hi Serene,


Thanks for feedback.


Three(3)in (B2+3) was added so that it starts looking value from/into the fourth column because in the first two columns were your "Range" values and in third were no.s (1~5). Thus the Formula is not hard coded. It is flexible to adjust according to Rank(1~5) you choose in B2!!


If you meant to say that you have got two ranks one that is present as Rank-1~5 (in row, at top of the table) and the other to the right (Nos. 1~5), then why should we look for Point Range (The first two columns in my sheet) as 0-50 will always be 01, 50-50 will always be 02, 51-69 always be 3 and so on. Isn't it so?


Regards,

FASEEH
 
Dear Faseeh,


My apologies for my misunderstanding.

I had thought the B2+3 is only for the 3rd following column. Now I understand it is "from" the 3rd following column

yes, it is definitely working now. All I can say is that you rock!


Best regards

Serene
 
Dear Narayan,


Thank you so much for your worksheet!

It has helped me understand another way around the problem.


Really appreciate your patience,

Cheers

Serene
 
You must log in or register to reply here.
Back
Top