vlookup no one can solve

  • Thread starter Thread starter jpx
  • Start date Start date
J

jpx

I am trying to use vlookup to return a value, in a specified column,
only if it meets a certain condition. For example in the list below I
want to search for test2 and return the itme in column 2. Currently if
vlookup search's for test2, it takes the first time it appears, and
returns the corresponding column entry, in this case "1". I want it to
keep looking until the value in column two is greater than say 4. IE
how can I search for test2 in this example and return 5. I have tried
to embed if then statements but have failed so far? Thanks

Test1 2
Test1 3
Test1 4
Test2 1
Test2 2
Test2 3
Test2 4
Test2 5
Test2 6
Test2 7
 
Using that example, what is the formula that solves the OP's illustration?

Alan Beban
 
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, the data
is in A1:B10, and the values in the second column are sorted in
ascending order:

=MIN(IF(VLookups("Test2",A1:B10,2)>4,vlookups("Test2",A1:B10,2)))

Alan Beban
 
I put your data in A1:B10 and test2 in E1

And this seemed to work ok:

=INDEX(B1:B10,MATCH(1,(A1:A10=E1)*(B1:B10>4),0))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
 
Back
Top