Vlookup?

%3CLINGO-SUB%20id%3D%22lingo-sub-2277348%22%20slang%3D%22en-US%22%3EVlookup%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2277348%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%20Anyone%20know%20how%20to%20make%20vlookup%20not%20just%20return%20a%20value%20from%20a%20column%2C%20but%20to%20compare%20if%20it%20is%20equal%20or%20bigger%20to%20another%20value%20before%20returning%20the%20result%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2277348%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2277389%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2277389%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029652%22%20target%3D%22_blank%22%3E%40RoseAnnkl%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20wish%20to%20exclude%20small%20values%20from%20the%20search%2C%20you%20could%20remove%20terms%20from%20the%20lookup%20array.%26nbsp%3B%20That%20would%20allow%20the%20look%20up%20to%20pass%20over%20invalid%20records%20and%20return%20a%20later%20match%20or%2C%20if%20that%20is%20not%20present%2C%20use%20the%20error%20to%20trigger%20an%20entirely%20different%20formula.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20XLOOKUP(Selected%2C%20IF(Value%26gt%3Bthreshold%2C%20Name)%2C%20Value%2C%20%22Not%20found%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20824px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F273640i29DC0D183A2E3A3A%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2277353%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2277353%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20could%20use%20it%20in%20an%20IF%20statement%3A%3C%2FP%3E%3CP%3E%3DIF(VLookup(...)%26gt%3B%3DValue%2C%20VLookup(...)%2C%20False)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20example%2C%20I%20have%20the%20IF%20statement%20just%20returning%20False%20if%20the%20returned%20value%20is%20%26lt%3B%3D%20the%20value%20you're%20testing%20it%20against.%20But%2C%20you%20can%20change%20that%20to%20whatever%20you%20want%2C%20such%20as%200%2C%20%22%22%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%2C%20by%20chance%2C%20you%20want%20the%20cell%20value%20to%20be%20the%20value%20you're%20testing%20for%20when%20the%20returned%20value%20is%26nbsp%3B%20%26lt%3B%20said%20value%2C%20then%20you%20might%20also%20use%20the%20MIN%20function%3A%3C%2FP%3E%3CP%3E%3DMIN(VLookup(...)%2C%20OtherValue)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi! Anyone know how to make vlookup not just return a value from a column, but to compare if it is equal or bigger to another value before returning the result?

2 Replies

You could use it in an IF statement:

=IF(VLookup(...)>=Value, VLookup(...), False)

 

In this example, I have the IF statement just returning False if the returned value is <= the value you're testing it against. But, you can change that to whatever you want, such as 0, "", etc.

 

If, by chance, you want the cell value to be the value you're testing for when the returned value is  < said value, then you might also use the MIN function:

=MIN(VLookup(...), OtherValue)

 

@RoseAnnkl 

If you wish to exclude small values from the search, you could remove terms from the lookup array.  That would allow the look up to pass over invalid records and return a later match or, if that is not present, use the error to trigger an entirely different formula.

= XLOOKUP(Selected, IF(Value>threshold, Name), Value, "Not found")

image.png