Forum Discussion

AShah16's avatar
AShah16
Copper Contributor
Feb 10, 2022
Solved

Can a LookUp Value in Vlookup be a condition?

Hi, Can a Lookup Value in Vlookup be a condition ?   Something to the effect of  =VLOOKUP(">0.1",D21:F109,3,TRUE)   What i am trying to achieve: 1. Find the first cell in Column D which has va...
excel
  • HansVogelaar's avatar
    HansVogelaar
    Feb 10, 2022

    AShah16 

    This is because your ranges don't begin in row 1. In F2:

     

    =INDEX(A3:A13,MIN(IF(D3:D13>4,ROW(D3:D13)-ROW(D3)+1)))

     

    and in F17:

     

    =INDEX(A17:A27,MIN(IF(D17:D27>4,ROW(D17:D27)-ROW(D17)+1)))

HansVogelaar's avatar
HansVogelaar
MVP
Feb 10, 2022

AShah16 

 

=INDEX(A1:A100,MIN(IF(D1:D100>4,ROW(D1:D100))))

 

Modify the ranges as needed. If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.

  • AShah16's avatar
    AShah16
    Copper Contributor
    Feb 10, 2022

    HansVogelaar 

    Hello,

    I tried the formula and got some errors. Any idea what could be wrong ?

     

    The answer for first table should have been 0.3 but i got 0.4. The second one did not give a result. I have tried Ctrl+Shift+Enter as well

    Thanks

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Feb 10, 2022

      AShah16 

      This is because your ranges don't begin in row 1. In F2:

       

      =INDEX(A3:A13,MIN(IF(D3:D13>4,ROW(D3:D13)-ROW(D3)+1)))

       

      and in F17:

       

      =INDEX(A17:A27,MIN(IF(D17:D27>4,ROW(D17:D27)-ROW(D17)+1)))

Resources