Lookup selective from another sheet

J

Jim

Assume I have one sheet as below. How can I create a new sheet and display
only those entrys that are greater than a entered value.

i.e main sheet

abc 2
def 5
ghi 6
jkl 5
fgh 3
krk 4

on second sheet, if 4 is entered only entrys >4 are shown. i.e

def 5
ghi 6
jkl 5
krk 4
 
B

Barb Reinhardt

Try something like this:

C1=IF(VLOOKUP(A1,Sheet2!A$1:B$4,2,FALSE)>Sheet1!B1,VLOOKUP(Sheet2!A$1:B$4,2,FALSE),NA())

Your main sheet is A1-b6 and Sheet 2 is A1:B4
 
M

Max

Another play to try ..

Assume this in Sheet1, in A1:B6
abc 2
def 5
ghi 6
jkl 5
fgh 3
krk 4
Click to expand...

Put in D1: =IF(Sheet2!A1="","",Sheet2!A1)
Put in C1: =IF(B1="","",IF(B1>=$D$1,ROW(),""))
Copy C1 down to say, C10, to cover the max expected data in col B

In Sheet2
------
The input cell for the value is A1
Enter in A1: 4

Put in A2:
=IF(ISERROR(SMALL(Sheet1!$C:$C,ROWS($A$1:A1))),"",
INDEX(Sheet1!A:A,MATCH(
SMALL(Sheet1!$C:$C,ROWS($A$1:A1)),Sheet1!$C:$C,0)))

Copy A2 across to B2, fill down to B11
(cover the same range size as done in col C in Sheet1)

The desired results based on the number entered in A1
will be returned from Sheet1 within A2:B11,
all neatly bunched at the top, viz. you'd get:
def 5
ghi 6
jkl 5
krk 4
Click to expand...
(Blank rows below)

Note that the assumed criteria placed in Sheet1's col C
is to select only entries in col B >= 4,
and not .. "only entrys >4" as posted

Adapt to suit ..
 
Top