=INDEX(Source!$J$2:$J$26,M

Final rank, also an array formula:

=IF(AND(B2="Privacy & Secure Data",F2="Less than 10,000",OR(G2="Weekly",G2=

Thomas

Solved

Posted on 2012-09-18

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

-Spend

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

-Frequency

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

Copy-of-Book1.xlsx

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

-Spend

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

-Frequency

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

Copy-of-Book1.xlsx

18 Comments

=INDEX(Source!$J$2:$J$26,M

Final rank, also an array formula:

=IF(AND(B2="Privacy & Secure Data",F2="Less than 10,000",OR(G2="Weekly",G2=

Thomas

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

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.

Copy-of-Copy-of-Book1.xlsx

for example:

=INDEX(Source!$J$2:$J$26,M

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.

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 ?

Testing101.xlsx

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.

Copy-of-Testing101.xlsx

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:

=IF(ISNA(VLOOKUP(CONCATENA

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.

Final-Test.xlsx

=VLOOKUP(CONCATENATE(B2,C2

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

FROM:

VLOOKUP(CONCATENATE(B2,C2,

TO:

VLOOKUP(CONCATENATE(B2,C2,

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.

i am using the following formula

=IF(ISNA(VLOOKUP(CONCATENA

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

Getting an FALSE response

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.

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 :

=IF(ISNA(VLOOKUP(CONCATENA

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.

screenshot-excel.jpg

Copy-of-Final-Test.xlsx

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

Final-Test-1.xlsx

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:

PHI1

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.

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.

By clicking you are agreeing to Experts Exchange's Terms of Use.

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 |

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

Connect with top rated Experts

**14** Experts available now in Live!