LoopUp

Flemming Dahl

unread,

Feb 8, 2001, 11:10:27 AM2/8/01

to

Hi all

When i use some of excel's functions to lookup date in a table, and it
doesn't exists, then excel returns the value right under the one i'd like to
find.

Is there any way i can get the value right OVER the one i'd like to find ?

Thanks,
Flemming

Ctrl-Alt-Dlt-Hlp

unread,

Feb 8, 2001, 12:30:26 PM2/8/01

to

Without knowing the values you are working with, some variation of this
may work for you:
=VLOOKUP(B10,D10:E18,2,TRUE)+1
HTH
In article <eS09xoekAHA.1136@tkmsftngp04>,

--
Ctrl-Alt-Dlt-Hlp ô)ô

Sent via Deja.com
http://www.deja.com/

Flemming Dahl

unread,

Feb 8, 2001, 1:18:44 PM2/8/01

to

NOPE

Ctrl-Alt-Dlt-Hlp <wils...@my-deja.com> skrev i en
nyhedsmeddelelse:95ul3j$1uf$1...@nnrp1.deja.com...

Niek Otten

unread,

Feb 8, 2001, 3:38:36 PM2/8/01

to

Hi Flemming,

Make your data table sorted descending and use the MATCH() function. MATCH()
works something like VLOOKUP(), but it can use descending sorted tables. It
does not return the item you look for, bur the row number within the table,
so you'll have to use INDEX() to get the item itself.

Example:

Table is in A1:B3:

5 a
3 b
1 c

value to look for is in C1.

Formula:

=INDEX(A1:B3,MATCH(C1,A1:A3,-1),2)

Look in HELP for details of INDEX() and MATCH()

Regards,

Niek Otten

Flemming Dahl <fle...@post3.tele.dk> schreef in berichtnieuws
eS09xoekAHA.1136@tkmsftngp04...

Flemming Dahl

unread,

Feb 8, 2001, 3:43:13 PM2/8/01

to

The data can't be sorted descending, so this can't work for me.

Thanks,
Flemming

Niek Otten <nico...@xs4all.nl> skrev i en
nyhedsmeddelelse:95v05n$5fa$1...@news1.xs4all.nl...

Tim Zych

unread,

Feb 8, 2001, 4:34:48 PM2/8/01

to

This worked in my tests, although it can probably be improved.

=INDIRECT(ADDRESS(MATCH(E1,A1:A50,0)*ROW(A1:A50)+1,COLUMN(A1)))
array-entered

It finds the first instance of E1 in A1:A30, returning the value in the next
cell below.

Flemming Dahl <fle...@post3.tele.dk> wrote in message
news:O9TxRBhkAHA.1696@tkmsftngp04...

Tim Zych

unread,

Feb 8, 2001, 4:41:37 PM2/8/01

to

> It finds the first instance of E1 in A1:A30

I meant A1:A50.

Also, you can subtract 1 (instead of add 1) after the ROW evaluation to
return the cell *before* the first instance of E1 (in which case a found
value in A1 would logically result in the #VALUE error).

Tim Zych <Tim_...@rsco.com> wrote in message
news:O$cD3bhkAHA.2168@tkmsftngp03...

Flemming Dahl

unread,

Feb 8, 2001, 5:03:10 PM2/8/01

to

Thanks Tim

When i got it translated right - i got it to work

This works too :-)

Function PopSlag(x As Range, P As Range, Kolonne)
Dim Testops As Variant
On Error Resume Next
Testops = WorksheetFunction.Match(x, P.Columns(1), 0)
If Err = 0 Then
PopSlag = P(Testops, Kolonne)
Else
Testops = WorksheetFunction.Match(x, P.Columns(1), 1)
PopSlag = P(Testops + 1, Kolonne)
End If
End Function

Thanks
Flemming

Tim Zych <Tim_...@rsco.com> skrev i en
nyhedsmeddelelse:efzvqfhkAHA.2164@tkmsftngp05...

Stewart Fizia

unread,

Feb 8, 2001, 6:46:10 PM2/8/01

to

AFAIK the situation you refer to is normal for Excel's LOOKUP function.
If an exact match is not found Excel will return the value found
immediately below that you are looking for

This is an example of a formula that I use

=IF(ISNA(MATCH(A1,Prospectus_No,0)),"No Match",VLOOKUP(A1,Prospectus,5))

This will look up a table called Prospectus where the first column is
called Prospectus_No and return the value in column 5 of the table
having found an exact match. If an exact match with the value in A1 is
not found the formula returns the text No Match

Hope this helps

Tim Zych

unread,

Feb 9, 2001, 5:39:10 PM2/9/01

to

> although it can probably be improved.

This is more robust:

=INDEX(A4:A19,MATCH(E1,A4:A19,0)+1,1)

Not array-entered.

Where the table array is A4:A19 and lookup value is E1. Add or subtract 1 to
the MATCHed evaluation to return the cell value below or above the matched
cell.

Tim Zych <Tim_...@rsco.com> wrote in message
news:O$cD3bhkAHA.2168@tkmsftngp03...