CODEWITHSUNDEEP
rexcelindexingmatchlookup
user36176
user36176

Reputation: 339

Equivalent of index - match in Excel to return greater than the lookup value

In R I need to perform a similar function to index-match in Excel which returns the value just greater than the look up value.

Data Set A

Country     GNI2009           
Ukraine     6604
Egypt       5937
Morocco     5307
Philippines 4707
Indonesia   4148
India       3677
Viet Nam    3180
Pakistan    2760
Nigeria     2699

Data Set B 

GNI2004 s1  s2  s3  s4
6649    295 33  59  3
6021    260 30  50  3
5418    226 27  42  2
4846    193 23  35  2
4311    162 20  29  2
3813    134 16  23  1
3356    109 13  19  1
2976    89  10  15  1
2578    68  7   11  0
2248    51  5   8   0
2199    48  5   8   0

At the 2009 level GNI for each country (data set A) I would like to find out which GNI2004 is just greater than or equal to GNI2009 and then return the corresponding sales values (s1,s2...) at that row (data set B). I would like to repeat this for each and every Country-gni row for 2009 in table A.

For example: Nigeria with a GNI2009 of 2698 in data set A would return:

GNI2004 s1  s2  s3  s4
2976    89  10  15  1

In Excel I guess this would be something like Index and Match where the match condition would be match(look up value, look uparray,-1)

Upvotes: 1

Views: 608

Answers (1)

David Arenburg
David Arenburg

Reputation: 92292

You could try data.tables rolling join which designed to achieve just that 

library(data.table) # V1.9.6+
indx <- setDT(DataB)[setDT(DataA), roll = -Inf, on = c(GNI2004 = "GNI2009"), which = TRUE]
DataA[, names(DataB) := DataB[indx]]
DataA  
#        Country GNI2009 GNI2004  s1 s2 s3 s4
# 1:     Ukraine    6604    6649 295 33 59  3
# 2:       Egypt    5937    6021 260 30 50  3
# 3:     Morocco    5307    5418 226 27 42  2
# 4: Philippines    4707    4846 193 23 35  2
# 5:   Indonesia    4148    4311 162 20 29  2
# 6:       India    3677    3813 134 16 23  1
# 7:    Viet Nam    3180    3356 109 13 19  1
# 8:    Pakistan    2760    2976  89 10 15  1
# 9:     Nigeria    2699    2976  89 10 15  1

The idea here is per each row in GNI2009 find the closest equal/bigger value in GNI2004, get the row index and subset. Then we update DataA with the result.

See here for more information.

Upvotes: 2

Related Questions