+ Reply to Thread
Results 1 to 16 of 16

Using multiple IF functions with VLOOKUP

  1. #1
    Registered User
    Join Date
    12-10-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    64

    Using multiple IF functions with VLOOKUP

    Hi I think this may be a tricky one, so I'd really appreciate any help. On the 'Cohort' tab in G5 I am trying to write a formula that will work out a grade based on what is in columns E2 and C5. I can do it for one specific combination of rules, but am struggling to work out how to make it apply to others. The combinations are as follows:

    E2 C5
    L F
    L H
    R F
    R H
    W F
    W H


    The L/R/W stand for 'Listening', 'Reading' and 'Writing'. The F and H stand for 'Foundation' and 'Higher'. The idea is that if a student, for example sits a Foundation Writing paper the VLOOKUP would look under the 'Ref' tab in columns W to Y to give out a grade from U to 5. Can this even be done with so many combinations?
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Using multiple IF functions with VLOOKUP

    Right up my street! Just retired from being a HoD MFL. Are you still using Excel 2010?

    Looking at your workbook, I think you have overcomplicated this - which exam board do you use? I am happy to anonymise my mock exam workbook that uses AQA grade boundaries.
    Last edited by AliGW; 07-16-2021 at 06:12 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Using multiple IF functions with VLOOKUP

    Attached is a desensitised sample mock workbook (I created this a couple of years back to share on one of the Facebook MFL pages). Within this book, you will see how to write a formula that looks up either an F Tier grade or an H Tier one depending on a setting in a column on the left.

    Let me know if you need any help adapting it, but just to show that what you want to do is entirely possible.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-10-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: Using multiple IF functions with VLOOKUP

    Ah hello colleague! This is for general assessments, rather than exams which is why I have based it on percentages (all of our tests are out of different marks). I'm going to have a look at your spreadsheet now - much appreciated!

  5. #5
    Registered User
    Join Date
    12-10-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: Using multiple IF functions with VLOOKUP

    Sorry and yes it's AQA! What do your SM/RM/MSM codes stand for?

  6. #6
    Registered User
    Join Date
    12-10-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: Using multiple IF functions with VLOOKUP

    Ah yes I've had a look through, but it's not really what i'm looking for - I have a similar one for Mocks etc, but thank you I really appreciate it

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Using multiple IF functions with VLOOKUP

    As example I named REF columns G:I as "LF_TBL"

    in G5

    =VLOOKUP($F5,INDIRECT($E$2&$C5&"_TBL"),3,FALSE)

    so E2 and C5 ("L" and "F") are used to define the named range

    Repeat for your other tables.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-10-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: Using multiple IF functions with VLOOKUP

    Thank you for having a go - I just tried to change C5 to "H" and it gave me a Ref error - any ideas what i'm doing wrong?

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Using multiple IF functions with VLOOKUP

    You need to create named ranges for ALL the combinations so K:M will be the "LH_TBL". I only defined the first one.

    Look at "Formulas"==>"Name Manager" to see how these are defined.

    Attached now has "LH" range defined
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    12-10-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: Using multiple IF functions with VLOOKUP

    Ah sorry, thank you - I have never used the name manager before!

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Using multiple IF functions with VLOOKUP

    All tables except last 2 ("F" / "H" )defined
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    12-10-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: Using multiple IF functions with VLOOKUP

    Ah yes, this works a treat! Thank you!

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Using multiple IF functions with VLOOKUP

    You're welcome.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED (I will correct this for you today).

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  14. #14
    Registered User
    Join Date
    12-10-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: Using multiple IF functions with VLOOKUP

    I already tried to "add reputation" but it is telling me that I need to spread some around before giving it to you again! Will mark as solved now

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Using multiple IF functions with VLOOKUP

    No problem !!!

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Using multiple IF functions with VLOOKUP

    Quote Originally Posted by codyryan View Post
    Ah hello colleague! This is for general assessments, rather than exams which is why I have based it on percentages (all of our tests are out of different marks). I'm going to have a look at your spreadsheet now - much appreciated!
    OK - that wasn't the point, though. I was just showing you how to deal with the grading formula. Glad John had your back - I went off to mow the lawn!

    Once you have given reputation to someone else, you should be able to rep John again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to combine multiple IF functions with a VLOOKUP?
    By clogistics in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-13-2016, 01:01 AM
  2. Not sure if I should use multiple vlookup, index or IF functions
    By nickiv in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-20-2013, 11:29 AM
  3. Multiple IF Functions or VLOOKUP functions
    By yinka00000 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-15-2012, 11:29 PM
  4. [SOLVED] Multiple if functions or vlookup
    By jmjulie25 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-13-2012, 08:58 AM
  5. Using multiple IF, AND, VLOOKUP functions
    By kushal4698 in forum Excel General
    Replies: 9
    Last Post: 03-12-2012, 09:42 AM
  6. [SOLVED] if statements depending on multiple VLOOKUP functions
    By njuneardave in forum Excel General
    Replies: 1
    Last Post: 06-21-2006, 11:40 AM
  7. [SOLVED] Multiple IF & VLOOKUP functions
    By Serge in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 03-10-2006, 12:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1