GOOGLE SHEETS: IF & AND determining VLookup result

mtaylor

Board Regular
Joined
May 1, 2013
Messages
78
Platform
  1. Windows
  2. MacOS
Hi all,

Ok so...

We are looking at two sets of data results. One for Expected Progress and one for Accelerated Progress.


If the Expected Progress is greater than 75% we move on to the Accelerated Progress cell.

If the Accelerated Progress cell is less than 50% the outcome is ACCEPTABLE
If the Accelerated Progress cell is between 50% and 61% the outcome is GOOD
If the Accelerated Progress cell is between 61% and 74% the outcome is VERY GOOD
If the Accelerated Progress cell is above 75% the outcome is OUTSTANDING


If the Expected Progress is between 15% and 75% the outcome is WEAK
If the Expected Progress is less than 15% the outcome is VERY WEAK


I'm hoping this is all the information needed, I did begin to have a look but my brain fried within 2 minutes - sorry.

Many thanks in advance

Matt
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I also suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be. From your description I cannot easily set up sample data and decide what you are trying to do.
 
Upvote 0
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I also suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be. From your description I cannot easily set up sample data and decide what you are trying to do.

Thanks Peter - my apologies

Very roughly, this is how it would look, with the vlookup tables on a different tab.

The outcomes to appear in the Outcome column based upon the results of the progress tallys.

Expected ProgressAccelerated ProgressOutcomeExpected OutcomesAccelerated Outcomes
80%40%Outstanding75%Outstanding
76%55%Very Good61%Very Good
77%34%Good 50%Good
82%60%75%AcceptableAcceptable
15%WeakWeak
0%Very WeakVery Weak
 
Upvote 0
This is what I have atm:

Excel Formula:
=IF(A3>"75%",VLOOKUP(B3,I3:J8,2,FALSE),VLOOKUP(A3,F3:G8,2,FALSE))

But it returns a N/A
 
Last edited by a moderator:
Upvote 0
I assume that you are trying to fill the column headed 'Outcome'?
If so, could you fill it in manually (after ensuring there are a variety of the scenarios you described in post 1 are present) and post that?
sample data & expected results to make it easier for helpers to understand

You also still haven't taken this on board ..
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
.. and it is relevant since the options available to you are dependant on your version(s)!
 
Upvote 0
This is what I have atm:

=IF(A3>"75%",VLOOKUP(B3,I3:J8,2,FALSE),VLOOKUP(A3,F3:G8,2,FALSE))

But it returns a N/A
All sorted:

Expected ProgressAccelerated ProgressOutcomeExpected OutcomesAccelerated Outcomes
90%67%Very Good0%Very Weak0%Acceptable
70%55%Weak15%Weak50%Good
77%34%Acceptable100%Acceptable61%Very Good
82%65%Very Good75%Outstanding

Excel Formula:
=(IF(A3>75%,VLOOKUP(B3,$I$3:$J$6,2,TRUE),VLOOKUP(A3,$F$3:$G$5,2,TRUE)))
 
Last edited by a moderator:
Upvote 0
Solution
I assume that you are trying to fill the column headed 'Outcome'?
If so, could you fill it in manually (after ensuring there are a variety of the scenarios you described in post 1 are present) and post that?


You also still haven't taken this on board ..

.. and it is relevant since the options available to you are dependant on your version(s)!

Thanks Peter,

I'm afraid I'm one of the Google Sheets users, I use the main forum as most of my problems are basic enough to be solved just through formula use.

I use the main excel one over the other option as this one is far more active and my issues are solved quickly.

My apologies

Matt
 
Upvote 0
I'm afraid I'm one of the Google Sheets users,
Well, unfortunately, I am not so I cannot help further. :(

Also note that the Excel Questions forum specifically states that it is not to be used for Google Sheets questions, so I have moved the question to the appropriate forum.
1605172256874.png


If your future questions are for Google Sheets please ask them in the correct forum from the start.
 
Upvote 0

Forum statistics

Threads
1,223,631
Messages
6,173,464
Members
452,516
Latest member
archcalx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top