Apr 18 2021 08:28 AM
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?
Apr 18 2021 08:32 AM - edited Apr 18 2021 08:35 AM
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)
Apr 18 2021 09:34 AM
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")