Excel Formula

Posted on 2012-09-18
Last Modified: 2012-09-20
Hey guys. I would greatly appreciate anyone that can help me out.
Not sure which formula is needed. and/if or V looking

1) I am looking for a formula that will determine the Initial Rank.
Initial Rank will be determined by the following
-Type of Data
-Tier Level

I created a table in the spreadsheet that list all the possible
25 combinations and the appropriate rank.

2) I am also looking for a formula that will determine the Final Rank.
Final Rank will be determined by the following
-Number of Customers
-*Type of data*

I created a table in the spreadsheet that list all the possible
combinations and the appropriate rank.

***Please note: If the "Number of Customers" is "Less than 10,000 and the "Frequency" is "Daily" or "Weekly" and the Type of Data is" Privacy & Secure Data" than the Final Rank
will be High. ***

In all other instances the Type of data will not matter.

Please see attachment. I have uploaded the spreadsheet.

combinations is in the source tab

Thank you guys.
Question by:sammenace
    LVL 39

    Expert Comment

    Initial rank, entered with Ctrl+Shift+Enter as an array formula:


    Final rank, also an array formula:

    =IF(AND(B2="Privacy & Secure Data",F2="Less than 10,000",OR(G2="Weekly",G2="Daily")), "High",INDEX(Source!$O$3:$O$26,MATCH(F2&G2&"NA",Source!$L$3:$L$26&Source!M3:M26&Source!$N$3:$N$26,0)))


    Author Comment


    Not familiar with array formulas.

    How do i copy the Initial Rank and Final Rank formulas
    into Column E (initial Rank) and Column H (Final Rank)

    I keep getting a #VALUE! error
    LVL 19

    Expert Comment

    by:Ken Butters
    I added a some columns on source tab to create a lookup key.. then used vlookup.

    see attached

    Note: also converted the two tables to tables.  This allows auto update of range if you add to table by putting cursor in last cell of table and hitting tab.
    LVL 19

    Expert Comment

    by:Ken Butters
    to update array formulas.... first copy nutcsh formula to clipboard...

    for example:

    then select cell e2

    Paste in formula

    then while holding down the Ctrl key and the shift key... hit enter

    Then just fill down.

    Repeat for cell H2 and other formula.

    Author Comment

    Let me test this out and i'll let you guys know

    thank you so much guys!

    Author Comment

    Hey guys thank you for both of your help. Forumla seems to be ok.
    I did notice one error

    In column F if the Number of Records is "Less than 10,000" and the Freuency is "Daily" or "Weekly" the Final Rank will be "High" if the Type of Data" is PHI, SPHI or PHI & SPI from column B. (This is what i wanted)

    My question is if the Type of Data is "BCI" or "No Sensitive Data"  in column B and the Number of Records is "Less than 10,000 in column F and the frequency is "Daily" or "Weekly" I need the Final Rank to show up as "Medium" and not "High" as it currently does.

    In other words:
    If the "Type of data" is PHI, SPI or PHI & SPI and the Number of records is "Less than 10,000 and the frequency is Daily or Weekly the Final Rank should be HIGH

    However; if the "Type of data" is BCI or No Sensitive Data and the Number of records is "Less than 10,000 and the frequency is Daily or Weekly the Final Rank should be Medium

    Is there a formula that ties column B with column F & G to get the final results in column H ?
    LVL 19

    Expert Comment

    by:Ken Butters
    in the second table... the type of data set to NA... i am assuming that means that the type of data is not used to determine final rank.

    I changed the formula in column 1 of that table... where I create the lookup key... to NOT make it part of the lookup key if the value is NA.

    otherwise it will be part of the lookup key.

    I think that change ... in combination with building the same key on the source page should do it...

    However... you will need to add additinal rows in the second table to account for the fact that you want those values also for PHI.... and SPI.  There are currently no rows with data that matches that criteria in the second table... so the lookup is blank for those... with the copy I am attaching.

    Author Comment


    Thank you once again for your assistance.

    1) I did make a user error. I decided that i do not need the "Spend" column.
    I deleted the column. I tried to update the formula but i am getting #REF! message.
    So the intial Rank will only be determied by Type of Data and Tier Level.

    The formula i put in is:


    2) You are right the NA are values that i do not want to include in the Final rank.
    I did update the table so all the possible combinations are now in there if the type of data is PHI, SPI and PHI & SPI. I added the last 6 rows in the table.

    However after making the changes i dont know what the new formula would be.
    When i choose Number of Record and Frequency the final rank is empty. What is the formula?

    Thank you once again for your help.
    LVL 19

    Expert Comment

    by:Ken Butters
    Yes... I see the problem.. it is in the VLOOKUP function:

    first parameter creates the key to lookup:    CONCATENATE(B2,C2,)
    this takes whatever is in b2 and c2 and combines them to create a key

    second parameter is where to look: InitalRankLookup
    InitialRankLookup is a table... you can see how it is defined by going to formulas tab and looking at name manager

    3rd parameter is the problem... it tells which column of the IntitialRankLookup is to be returned when a match is found.... we used to have 5 columns... and we wanted the last one... now we have only 4 columns... so we need to make the following change


    The vlookup is used twice in the same formual... once just to check to see if it returns #NA and the second time to return the actual value... so you will need to change the 5 to 4 in both instances ... then fill down.  

    That will correct our #REF error.

    Author Comment

    The initial rank is working! however for the Final Rank isnt still getting an error

     i am using the following formula


    I updated the table so all the combinations are in there now.

    Getting an  FALSE response
    LVL 39

    Expert Comment

    Have you tried with the array formulas at all?

    Author Comment


    Thanks for checking in.

    I tried the array formula but i was told the VLOOKUP is better.
    I am new to excel so i don't have much knowledge in excel.

    I changed my tables as well. Took out the spend column in the initial rank.
    Also in the Final Rank i needed to add the value from column B (type of data)
    into the Number of Records and the Frequency to get the Final Rank

    I didnt ask for this request when i first submitted my question.
    LVL 19

    Expert Comment

    by:Ken Butters
    Looks like a new column was added to 2nd table which I didnt notice.

    In case you need to change it in the future... here is one way:

    Highlight existing table (K3:N16 for example)

    select design tab

    click on resize table (see screenshot)

    then specify the columns/rows to make sure all necessary data (including new column O) is included in definition of the table.

    I've also updated / attached spreadsheet with that change.

    Final rank formula for attached spreadsheet should be :


    Note: it is returning column 5 of the table...You will need to adjust formula if you are no longer using column 5 as value to return for final Rank.

    Author Comment


    I opened the excel spreadsheet and i notice even with the correct formula the Final Rank is blank. But the Final Rank appears if the Initial Rank is left empty and vice versa.

    In row 2 - when type of data, tier level is filled the initial rank is generated. when i fill in the
    number of record , frequency the final rank is blank

    any idea why Final Rank is not generating?

    please see attachment
    LVL 19

    Accepted Solution

    Yes... the two lookups are somewhat unrelated....

    the first lookup for initial rank builds a key from columns B & C ... so you take the two strings in columns B&C... and find a matching string in the first table in the "Key" column.  

    In your latest spreadsheet example... every instance of B & C exists in the first table... some examples:

    PHI & SPI5

    so that is why a match was found in every row for Initial Rank.

    For Final Rank:

    2nd lookup for final rank works in a similar fashion... but it builds a different key.. and that key derived by concatenating values from E2 + F2 + B2 and so on ....
    first key built is : "More than 10,000DailyPhi"

    If you look at the 2nd table... you do not have an entry for  "More than 10,000DailyPhi".. .so the final rank is blank... meaning no match found.

    If you had a row in the 2nd table where
    o Number of Records is "More than 10,000"
    o Freqency is "Daily"
    o Type of Data is "PHI"

    then you would have a match for the first row on sheet 1... and it would populate the final rank from that 2nd table on source tab.
    LVL 19

    Expert Comment

    by:Ken Butters
    Also just noticed this... in 2nd table on source sheet PHI&SPI have no spaces.
    on sheet1 ... under type of Data "PHI & SPI" have spaces...anything built using PHI&SPI would never match key for that reason... both of those need to match.

    Author Comment



    I love how you explained the issue. Really easy to understand.
    I greatly appreciate your help!

    Author Closing Comment

    Really helpful. Thanks again

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    Title # Comments Views Activity
    offset an array formula 4 23
    VBA to sum a column 13 30
    Vlookup for IP 3 33
    Dynamic Bar chart in Excel 5 0
    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now