# Lookup in columns and return if the condition is match

1. ## Lookup in columns and return if the condition is match

Search columns 1,2 and 3 and returns the result to all the data from column A to have a higher or lower number in Column3
Looking numerical line I need to find results that are within the allowable values

I want to search a range of data and return the name if the value inside of a specific value.
The rest of the problems I'm handling the VLOOKUP function
I need a formula that will return the names that meet the requirement. If necessary we can also add an extra help column.
If I am in cell G3 or K3 enter another number then the result should be adapted to the number of basic data

Can someone help me, my example was added to the post,
I need formula for cells G3:G11 and K3:K11  Register To Reply

2. ## Re: Lookup in columns and return if the condition is match

Could you shed some light let say on this:
B- 255-10%=229,5 no match 200<22%from255

So 200 is less than 299(255+22%) or 229,5 is less than 299?  Register To Reply

3. ## Re: Lookup in columns and return if the condition is match Originally Posted by RobertMika Could you shed some light let say on this
Hi RobertMika

200 is not in the the area of 10% compared to the 255
I have added a picture for easier understanding  Register To Reply

4. ## Re: Lookup in columns and return if the condition is match

How about this then? The workbook you posted is in excel 2003 format but since it says in your profile that you are using excel 2007 I post in that format. I think the IFERROR is the only function that I used that is not compatible with excel 2003,  Register To Reply

5. ## Re: Lookup in columns and return if the condition is match Originally Posted by Jacc Hi Jacc,
Your idea of ​​a solution is great if used files *.xlsx
But how to solve if using Excel 2003, is there a way?
Thank you for this solution

And therefore the problem is not resolved by the end.  Register To Reply

6. ## Re: Lookup in columns and return if the condition is match

If you meam the formula on column K, try this ARRAY....
=IF(ISERROR(INDEX(\$A\$2:\$A\$10,SMALL(IF(\$D\$2:\$D\$10<0,IF(\$D\$2:\$D\$10>=\$K\$2,ROW(\$A\$2:\$A\$10)-1)),ROW(F1)))),"-",INDEX(\$A\$2:\$A\$10,SMALL(IF(\$D\$2:\$D\$10<0,IF(\$D\$2:\$D\$10>=\$K\$2,ROW(\$A\$2:\$A\$10)-1)),ROW(F1))))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Please note, the reason we ask for the excel version you are using, is for this very reason. The member gave a formula that will work with 2007 - as per your profile - but it seems you need it for pre-2007?  Register To Reply

7. ## Re: Lookup in columns and return if the condition is match Originally Posted by FDibbins Please note, the reason we ask for the excel version you are using, is for this very reason. The member gave a formula that will work with 2007 - as per your profile - but it seems you need it for pre-2007?
Hi Dibbins
Formula is OK for Excel 2003

I want to mention.
It is true that I use Excel 2007, but I like to have a formula for both the version of Excel 2003 and 2007.
So I am pleased with the decision and the formula set by the Jacc and with this formula you're set.

Thank you once again. Problem solved  Register To Reply

8. ## Re: Lookup in columns and return if the condition is match

Glad to hear it works. Thanks for converting the formula, FDibbins.   Register To Reply

9. ## Re: Lookup in columns and return if the condition is match

With a small delay but here is my input
You do not realy need helper column:

=IFERROR(INDEX(\$A\$2:\$A\$10,SMALL(IF(\$D\$2:\$D\$10>=0,IF((\$C\$2:\$C\$10)/(\$B\$2:\$B\$10)-1<=\$G\$2,ROW(\$A\$2:\$A\$10)-1)),ROWS(\$F\$1:F1))),"-")
and is better to use ROWS not ROW to prevent errors in case a new column/row is added.
For Excel 2003:
F1:

=SUMPRODUCT(--(\$D\$2:\$D\$10>=0),--((\$C\$2:\$C\$10)/(\$B\$2:\$B\$10)-1<=\$G\$2))
G2:
=IF(ROWS(\$F\$1:F1)>\$F\$1,"-",INDEX(\$A\$2:\$A\$10,SMALL(IF(\$D\$2:\$D\$10>=0,IF((\$C\$2:\$C\$10)/(\$B\$2:\$B\$10)-1<=\$G\$2,ROW(\$A\$2:\$A\$10)-1)),ROWS(\$F\$1:F1))))
is much more faster.  Register To Reply