Forum Discussion

jboase1's avatar
jboase1
Copper Contributor
Apr 17, 2025

IFS with VLOOKUP help

IF(OR(IFERROR(IFS(AND(F3=VLOOKUP(F3,'Dropdown list'!N2),F4=VLOOKUP(F4,'Dropdown list'!N2)),"yes",AND(F3=VLOOKUP(F3,'Dropdown list'!O2:O3,1),F4=VLOOKUP(F4,'Dropdown list'!O2:O3,1)),"yes",AND(F3=VLOOKUP(F3,'Dropdown list'!P2:P4,1),F4=VLOOKUP(F4,'Dropdown list'!P2:P4,1)),"yes",AND(F3=VLOOKUP(F3,'Dropdown list'!Q2:Q4,1),F4=VLOOKUP(F4,'Dropdown list'!Q2:Q4,1)),"yes"),"no")="yes",IFERROR(IF(AND(F3=VLOOKUP(F3,'Dropdown list'!R2),F4=VLOOKUP(F4,'Dropdown list'!R2)),"yes"),"no")="yes"),"yes","no")

If 1st value is in column N and 2nd value is in column N, then return "yes" if not then return "no".  This is how I would like this to work.  Thoughts?

excel
Formulas and Functions

3 Replies

Sort By
  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Apr 17, 2025

    this is similar or same as another post (https://techcommunity.microsoft.com/discussions/excelgeneral/ifs-with-vlookup-not-functioning/4405362)

    In this case the Bilirubin Ranges are all unique number/values and assuming F3 and F4 are not blank, I would recommend:

    =IF(OR(MMULT({1,1,1},(F3=N2:R4)+(F4=N2:R4))=2),"yes","no")

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Apr 17, 2025

    You can use this shorter and more structured formula:

    =IF(
       OR(
          AND(COUNTIF('Dropdown list'!N:N, F3), COUNTIF('Dropdown list'!N:N, F4)),
          AND(COUNTIF('Dropdown list'!O:O, F3), COUNTIF('Dropdown list'!O:O, F4)),
          AND(COUNTIF('Dropdown list'!P:P, F3), COUNTIF('Dropdown list'!P:P, F4)),
          AND(COUNTIF('Dropdown list'!Q:Q, F3), COUNTIF('Dropdown list'!Q:Q, F4)),
          AND(COUNTIF('Dropdown list'!R:R, F3), COUNTIF('Dropdown list'!R:R, F4))
       ),
       "yes",
       "no"
    )

    If 1st value is in column N and 2nd value is in column N, then return "yes" if not then return "no".  This is how I would like this to work.  Thoughts?

    =IF(AND(COUNTIF('Dropdown list'!N:N,F3), COUNTIF('Dropdown list'!N:N,F4)),"N",
     IF(AND(COUNTIF('Dropdown list'!O:O,F3), COUNTIF('Dropdown list'!O:O,F4)),"O",
     IF(AND(COUNTIF('Dropdown list'!P:P,F3), COUNTIF('Dropdown list'!P:P,F4)),"P",
     IF(AND(COUNTIF('Dropdown list'!Q:Q,F3), COUNTIF('Dropdown list'!Q:Q,F4)),"Q",
     IF(AND(COUNTIF('Dropdown list'!R:R,F3), COUNTIF('Dropdown list'!R:R,F4)),"R",
     IF(OR(COUNTIF('Dropdown list'!N:R,F3), COUNTIF('Dropdown list'!N:R,F4)),"partial","no"))))))

     

    Hope I was able to help you with this information.

    • jboase1's avatar
      jboase1
      Copper Contributor
      Apr 17, 2025

      Hi Niko!  I just tried this and it worked!  Thank you very much!

Resources