Link to home
Create AccountLog in
Avatar of sammenace
sammenace

asked on

Excel - VLOOKUP Help

The final ranking needs to be determined by the following
Type of data
BIA Tier
Number of Records
Frequency

Currently the Final Rank is determined by
Type of data
Number of Records
Frequency

But not BIA Tier

How can i get the BIA Tier to be added into determining the Final Rank?


Please note that the Initial Rank is only determined by
Type of Data and BIA Tier

This is a followup to a question i asked a month ago
here is the original question

https://www.experts-exchange.com/questions/27869460/Excel-Formula.html


buttersk was kind enough to help me out in my original question.
Ranking-Process.xlsx
Avatar of NBVC
NBVC
Flag of Canada image

Update K2 in Source file to:

=IF(N2="NA",CONCATENATE(L2,M2,O2),CONCATENATE(L2,M2,N2,O2))

and copied down

then update H2 in Sheet1 to:

=IF(ISNA(VLOOKUP(CONCATENATE(E2,F2,B2,C2),FinalRankLookup,5,FALSE)),"",VLOOKUP(CONCATENATE(E2,F2,B2,C2),FinalRankLookup,5,FALSE))

copied down
Avatar of sammenace
sammenace

ASKER

NB_VC

I tried what you told me but in the final raking it says "Tier 1" instead of the rank such as critial.

please see attachment

any idea?
1-Ranking-Process.xlsx
Not for points......

presumably you could simplify that H2 formula by using IFERROR, i.e.

=IFERROR(VLOOKUP(E2&F2&B2&C2,FinalRankLookup,5,0),"")

regards, barry
ASKER CERTIFIED SOLUTION
Avatar of NBVC
NBVC
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hey guys thank you for your response but I think i am not being clear in my original question. I will update the values tonight and try to be more clear...thanks and ill post soon
Guys it worked!
I was right it was a user error in regards to my values.

NB_VC and Berry just want to say Thank you !

=IF(ISNA(VLOOKUP(CONCATENATE(E2,F2,B2,C2),FinalRankLookup,6,FALSE)),"",VLOOKUP(CONCATENATE(E2,F2,B2,C2),FinalRankLookup,6,FALSE))

=IFERROR(VLOOKUP(CONCATENATE(E2,F2,B2,C2),FinalRankLookup,6,FALSE),"")