Re: [R] [PS] Re: vlookup in R

From: Ben Fairbank <BEN_at_ssanet.com>
Date: Mon, 24 Mar 2008 10:32:27 -0500

Quite right, there is an optional 4th argument, and the table must be sorted ascending on the first column in Excel. Thus these functions only approximately duplicate the Excel functions (improve on them IMHO).

BTW, I pasted the wrong formula in my reply; though it works, simpler is

ID <- 4 #for example, find value corresponding to 4

x[x[,1]==ID,2]

Ben

-----Original Message-----
From: r-help-bounces_at_r-project.org [mailto:r-help-bounces_at_r-project.org] On Behalf Of David Winsemius
Sent: Monday, March 24, 2008 10:20 AM
To: r-help_at_stat.math.ethz.ch
Subject: [PS] Re: [R] vlookup in R

Sachin J <sachinj.2006_at_yahoo.com> wrote in news:897147.50760.qm_at_web37606.mail.mud.yahoo.com:

> Is there are function similar to excel vlookup in R. Please let me
> know.
>
Caveat: definition of VLOOKUP done from memory and by checking OO.o Calc function of same name. (Don't have Excel on this machine.)

VLOOKUP looks up a single value in the first column of an Excel range and returns a column value (offset by a given integer) from the first matching row in that "range". The indexing functions ("extract" or "[" ) can be used:

> df4

     V1 V2 V3

1  4.56  1 0.1
2  8.42  1 0.2
3  0.79  3 0.3
4  5.39  3 0.4
5  0.95  4 0.5
6  7.73  5 0.6
7  7.17  6 0.7
8  3.89  7 0.8
9  0.54 10 1.0
10 9.53  9 0.9

> df4[df4$V1==0.79,2]
[1] 3

vlookup <- function(val, df, row){

               df[df[1] == val, row][1] }

> vlookup(0.79, df4, 2)
[1] 3

I thought there was an optional 4th argument to VLOOKUP that specifies the action to be taken if there is no exact match. You may need to change the equality in that function to an inequality and identify the first column value that is less than or equal to "val". If I remember correctly, Excel assumes that the first column is ordered ascending.

-- 
David Winsemius

______________________________________________
R-help_at_r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

______________________________________________
R-help_at_r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Received on Mon 24 Mar 2008 - 16:13:49 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Mon 24 Mar 2008 - 17:30:23 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.

list of date sections of archive