SOLVED

Can a LookUp Value in Vlookup be a condition?

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3147747%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3ECan%20a%20LookUp%20Value%20in%20Vlookup%20be%20a%20condition%3F%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3147747%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EHi%2C%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3ECan%20a%20Lookup%20Value%20in%20Vlookup%20be%20a%20condition%20%3F%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3ESomething%20to%20the%20effect%20of%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(%22%26gt%3B0.1%22%2CD21%3AF109%2C3%2CTRUE)%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EWhat%20i%20am%20trying%20to%20achieve%3A%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E1.%20Find%20the%20first%20cell%20in%20Column%20D%20which%20has%20value%20greater%20than%204%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E2.%20Report%20corresponding%20cell%20value%20from%20Column%20A.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EAny%20Ideas.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EThanks%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F346965i9223B5A4F06056C1%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22Excel_Vlookup.png%22%20alt%3D%22%5C%26quot%3BExcel_Vlookup.png%5C%26quot%3B%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3147747%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
Occasional Contributor

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 value greater than 4

2. Report corresponding cell value from Column A.

 

Any Ideas.

Thanks

 

 

 

Excel_Vlookup.png

 

 

 

3 Replies

@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.

@Hans Vogelaar 

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

Excel_Error.JPG

best response confirmed by AShah16 (Occasional Contributor)
Solution

@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)))