+ Reply to Thread
Results 1 to 5 of 5

Lookup formula except include "other" as a varible

  1. #1
    Forum Contributor
    Join Date
    10-15-2013
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    218

    Lookup formula except include "other" as a varible

    Hello,

    Please see attached spreadsheet.

    I would like a formula in column G, that if matches/looks up J, K and L in the table next to it starting at N2.

    I have two undefined variables that I have called "other" and "level".

    "Other" is anything that is not listed in column N such as "XX" in my example.
    "Level" could be any number between 0-4. There are two columns P (for anything level 3) and Q (for anything other than 3).

    Let me know if this needs more explaination...

    Any help is appreciated!

    Thanks,
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,900

    Re: Lookup formula except include "other" as a varible

    Try this in G3:

    =IFERROR(LOOKUP(2,1/($N$3:$N$13=J3)/($O$3:$O$13=K3),IF(L3=3,$P$3:$P$13,$Q$3:$Q$13)),
    IFERROR(LOOKUP(2,1/($N$3:$N$13=J3)/($O$3:$O$13="Other"),IF(L3=3,$P$3:$P$13,$Q$3:$Q$13)),"Unknown"))

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,900

    Re: Lookup formula except include "other" as a varible

    Or try:

    =LOOKUP(2,1/($N$3:$N$14=IF(COUNTIF($N$3:$N$14,J3),J3,"Other"))/($O$3:$O$14=IF(COUNTIF($O$3:$O$14,K3),K3,"Other")),
    IF(L3=3,$P$3:$P$14,$Q$3:$Q$14))

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,577

    Re: Lookup formula except include "other" as a varible

    ARRAY formula in G3 then drag down.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  5. #5
    Forum Contributor
    Join Date
    10-15-2013
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    218

    Re: Lookup formula except include "other" as a varible

    Perfect. Thanks!

+ 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] Need help modifying multiple range code to include LastRow ("B2:B10",..) -> ("B2:B" & LR.)
    By sureng20 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-09-2017, 09:31 AM
  2. Help with lookup formula to return "Available" or "Unavailable" based on last entry
    By AntonyCole1979 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-07-2017, 05:14 PM
  3. [SOLVED] Help with lookup formula to return "Available" or "Unavailable" based on last entry
    By AntonyCole1979 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-07-2017, 12:28 PM
  4. How to make a Pivot Table "Top 10" include an "other" category.
    By Melvinrobb in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 09-09-2014, 05:15 AM
  5. Help Needed to get Filters Using "Include" and "Exclude" multiple values on one column.
    By alfykunable in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-23-2012, 04:03 AM
  6. how to adjust chart series formula to include the "indirect" function
    By luv2glyd in forum Excel Charting & Pivots
    Replies: 13
    Last Post: 06-13-2010, 07:35 AM
  7. Error msgs: "Object varible or with block variable not set"; "subscript out of range"
    By menyanthe in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-26-2009, 04:58 PM

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