INICIO

Cómo hacer un BUSCARV en R (y también INDICE COINCIDIR y BUSCARX)

Manipulación de datos con R Transformación de datos

BUSCARV en R con match, merge y left_join

En R no existe una función BUSCARV (VLOOKUP si tienes Excel en inglés), pero tampoco la necesitas: la función match, merge y left_join de dplyr hacen el mismo trabajo para toda una columna a la vez, sin arrastrar ninguna fórmula. En este tutorial aprenderás los tres métodos, cómo traer varias columnas de una vez, qué hacer con los #N/A (NA en R), por qué las claves duplicadas cambian el resultado, cómo hacer una coincidencia aproximada como BUSCARV(...; VERDADERO) y cómo pasar INDICE COINCIDIR y BUSCARX a R.

Respuesta rápida: BUSCARV en R

Si tienes una tabla ventas con una columna id_producto y una tabla productos con el precio de cada producto, la fórmula de Excel =BUSCARV(B2; productos!A:D; 4; FALSO) (en inglés, =VLOOKUP(B2, productos!A:D, 4, FALSE)) se escribe en R con cualquiera de estas líneas:

# R base: match() + indexación (devuelve un vector)
ventas$precio <- productos$precio[match(ventas$id_producto, productos$id_producto)]

# R base: merge() (devuelve un data frame)
merge(ventas, productos, by = "id_producto", all.x = TRUE)

# dplyr: left_join() (devuelve un data frame)
library(dplyr)
left_join(ventas, productos, by = "id_producto")

Usa match cuando solo necesites una columna y left_join cuando necesites varias columnas o ya estés trabajando con dplyr. El código es el mismo en RStudio que en cualquier otro editor: RStudio es solo la interfaz, las funciones son de R.

Datos de ejemplo

En este tutorial utilizaremos dos tablas pequeñas, como las que tendrías en dos hojas de un libro de Excel. La tabla productos es la tabla de búsqueda (la matriz del BUSCARV) y la tabla ventas es la tabla a la que quieres añadir la información. Fíjate en que el producto A05 no existe en la tabla de productos y en que A02 se ha vendido dos veces.

productos <- data.frame(id_producto = c("A01", "A02", "A03", "A04"),
                        producto = c("Teclado", "Ratón", "Monitor", "Webcam"),
                        categoria = c("Accesorios", "Accesorios", "Pantallas", "Vídeo"),
                        precio = c(25, 12, 180, 60))

ventas <- data.frame(pedido = 1:6,
                     id_producto = c("A02", "A01", "A03", "A05", "A02", "A04"),
                     unidades = c(3, 1, 2, 5, 4, 1))

productos
ventas

  id_producto producto  categoria precio
1         A01  Teclado Accesorios     25
2         A02    Ratón Accesorios     12
3         A03  Monitor  Pantallas    180
4         A04   Webcam      Vídeo     60

  pedido id_producto unidades
1      1         A02        3
2      2         A01        1
3      3         A03        2
4      4         A05        5
5      5         A02        4
6      6         A04        1

BUSCARV con match()

El equivalente más directo de un BUSCARV exacto en R base es la combinación de match e indexación. match(x, table) devuelve, para cada elemento de x, la posición de su primera coincidencia en table, o NA si no hay coincidencia:

match(ventas$id_producto, productos$id_producto)

[1]  2  1  3 NA  2  4

Lee la salida como “el primer pedido es del producto de la fila 2 de productos, el segundo del producto de la fila 1…” y así sucesivamente. El cuarto valor es NA porque A05 no está en la tabla de búsqueda. Una vez tienes los números de fila, puedes usarlos para extraer cualquier columna de la tabla de búsqueda:

# BUSCARV del precio
ventas$precio <- productos$precio[match(ventas$id_producto, productos$id_producto)]

ventas

  pedido id_producto unidades precio
1      1         A02        3     12
2      2         A01        1     25
3      3         A03        2    180
4      4         A05        5     NA
5      5         A02        4     12
6      6         A04        1     60

Esta única línea sustituye a un BUSCARV arrastrado por toda la columna. Además, a diferencia de Excel, no tienes que contar el número de columna (el 4 del BUSCARV): eliges la columna por su nombre, así que el código no se rompe si alguien inserta una columna nueva en la tabla de búsqueda.

Con el precio ya en la tabla, el resto de cálculos también son vectorizados. Por ejemplo, el total de cada pedido es:

ventas$total <- ventas$unidades * ventas$precio

ventas

  pedido id_producto unidades precio total
1      1         A02        3     12    36
2      2         A01        1     25    25
3      3         A03        2    180   360
4      4         A05        5     NA    NA
5      5         A02        4     12    48
6      6         A04        1     60    60

En los siguientes apartados partiremos de nuevo de la tabla ventas original, así que si estás ejecutando el código elimina las dos columnas nuevas con ventas$precio <- NULL y ventas$total <- NULL.

BUSCARV con merge()

La función merge une dos data frames por una columna común. Para conservar todas las filas de la tabla de ventas, como hace un BUSCARV, tendrás que indicar all.x = TRUE (un left join). Sin este argumento, los pedidos cuyo producto no se encuentra desaparecerían.

merge(ventas, productos, by = "id_producto", all.x = TRUE)

  id_producto pedido unidades producto  categoria precio
1         A01      2        1  Teclado Accesorios     25
2         A02      1        3    Ratón Accesorios     12
3         A02      5        4    Ratón Accesorios     12
4         A03      3        2  Monitor  Pantallas    180
5         A04      6        1   Webcam      Vídeo     60
6         A05      4        5     <NA>       <NA>     NA

merge añade todas las columnas de productos de una vez, pero fíjate en dos diferencias con Excel: la columna clave pasa a la primera posición y las filas se ordenan por la clave, así que el orden de las ventas ha cambiado. Si el orden original es importante, vuelve a ordenar el resultado con order:

res <- merge(ventas, productos, by = "id_producto", all.x = TRUE)
res[order(res$pedido), ]

  id_producto pedido unidades producto  categoria precio
2         A02      1        3    Ratón Accesorios     12
1         A01      2        1  Teclado Accesorios     25
4         A03      3        2  Monitor  Pantallas    180
6         A05      4        5     <NA>       <NA>     NA
3         A02      5        4    Ratón Accesorios     12
5         A04      6        1   Webcam      Vídeo     60

BUSCARV con left_join() de dplyr

La función left_join de dplyr es la opción más legible y la que más se usa hoy en día. Conserva todas las filas de la primera tabla en su orden original y añade las columnas de la segunda. Las filas sin coincidencia reciben NA, igual que con merge(all.x = TRUE). Puedes ver el resto de uniones en el tutorial de joins de dplyr.

library(dplyr)

ventas %>%
  left_join(productos, by = "id_producto")

  pedido id_producto unidades producto  categoria precio
1      1         A02        3    Ratón Accesorios     12
2      2         A01        1  Teclado Accesorios     25
3      3         A03        2  Monitor  Pantallas    180
4      4         A05        5     <NA>       <NA>     NA
5      5         A02        4    Ratón Accesorios     12
6      6         A04        1   Webcam      Vídeo     60

Si la columna clave tiene un nombre distinto en cada tabla (por ejemplo id_producto y codigo), utiliza by = join_by(id_producto == codigo).

Buscar varias columnas a la vez

En Excel necesitas un BUSCARV por columna (o un BUSCARV con una matriz de números de columna). En R, un join trae todas las columnas de la tabla de búsqueda, así que si solo quieres algunas, selecciónalas antes de unir con select:

ventas %>%
  left_join(productos %>% select(id_producto, producto, precio),
            by = "id_producto")

  pedido id_producto unidades producto precio
1      1         A02        3    Ratón     12
2      2         A01        1  Teclado     25
3      3         A03        2  Monitor    180
4      4         A05        5     <NA>     NA
5      5         A02        4    Ratón     12
6      6         A04        1   Webcam     60

En R base puedes calcular las posiciones una sola vez con match y usarlas para asignar varias columnas en un solo paso:

idx <- match(ventas$id_producto, productos$id_producto)
ventas_2 <- ventas
ventas_2[c("producto", "precio")] <- productos[idx, c("producto", "precio")]

ventas_2

  pedido id_producto unidades producto precio
1      1         A02        3    Ratón     12
2      2         A01        1  Teclado     25
3      3         A03        2  Monitor    180
4      4         A05        5     <NA>     NA
5      5         A02        4    Ratón     12
6      6         A04        1   Webcam     60

Valores no encontrados: #N/A en Excel y NA en R

Cuando un BUSCARV no encuentra el valor, Excel devuelve el error #N/A. R devuelve NA, el valor faltante, y sigue adelante. Es más cómodo, pero tienes que tenerlo en cuenta: como has visto antes, el total del pedido con precio NA también es NA, y una sum de esa columna devolvería NA salvo que indiques na.rm = TRUE. Aprende más sobre esto en el tutorial de valores NA en R.

El equivalente de SI.ERROR

El típico =SI.ERROR(BUSCARV(...); "No encontrado") (IFERROR en inglés) se hace en R reemplazando los NA después de la búsqueda. Con dplyr puedes usar coalesce, que devuelve el primer valor no faltante:

ventas %>%
  left_join(productos, by = "id_producto") %>%
  mutate(producto = coalesce(producto, "No encontrado"),
         precio = coalesce(precio, 0))

  pedido id_producto unidades      producto  categoria precio
1      1         A02        3         Ratón Accesorios     12
2      2         A01        1       Teclado Accesorios     25
3      3         A03        2       Monitor  Pantallas    180
4      4         A05        5 No encontrado       <NA>      0
5      5         A02        4         Ratón Accesorios     12
6      6         A04        1        Webcam      Vídeo     60

En R base la misma idea se escribe con ifelse e is.na:

p <- productos$producto[match(ventas$id_producto, productos$id_producto)]
ifelse(is.na(p), "No encontrado", p)

[1] "Ratón"         "Teclado"       "Monitor"       "No encontrado"
[5] "Ratón"         "Webcam"

Piénsalo dos veces antes de sustituir un precio que falta por 0. En Excel es habitual ocultar los #N/A con SI.ERROR, pero un precio de 0 no es lo mismo que un precio desconocido: bajará cualquier media que calcules. Reemplaza los NA solo cuando el reemplazo tenga sentido, y mira antes qué claves han fallado.

Encontrar las claves que no coinciden

Antes de ocultar los NA conviene listar las filas que no han encontrado coincidencia. anti_join devuelve las filas de la primera tabla sin coincidencia en la segunda. En R base puedes usar %in%:

anti_join(ventas, productos, by = "id_producto")

# R base
ventas[!ventas$id_producto %in% productos$id_producto, ]

  pedido id_producto unidades
1      4         A05        5

  pedido id_producto unidades
4      4         A05        5

Claves duplicadas: primera coincidencia o todas

Esta es la diferencia más importante entre BUSCARV y un join. Imagina que la tabla de productos tiene el producto A02 dos veces, con un precio antiguo y uno nuevo:

productos_dup <- rbind(productos,
                       data.frame(id_producto = "A02", producto = "Ratón (nuevo)",
                                  categoria = "Accesorios", precio = 15))
productos_dup

  id_producto      producto  categoria precio
1         A01       Teclado Accesorios     25
2         A02         Ratón Accesorios     12
3         A03       Monitor  Pantallas    180
4         A04        Webcam      Vídeo     60
5         A02 Ratón (nuevo) Accesorios     15

Un BUSCARV devuelve la primera coincidencia e ignora el resto. match se comporta exactamente igual, así que los pedidos de A02 reciben el precio 12:

productos_dup$precio[match(ventas$id_producto, productos_dup$id_producto)]

[1]  12  25 180  NA  12  60

Un join, sin embargo, devuelve todas las coincidencias, así que cada pedido de A02 se duplica y la tabla pasa de 6 a 8 filas. dplyr te avisa de ello:

left_join(ventas, productos_dup, by = "id_producto")

  pedido id_producto unidades      producto  categoria precio
1      1         A02        3         Ratón Accesorios     12
2      1         A02        3 Ratón (nuevo) Accesorios     15
3      2         A01        1       Teclado Accesorios     25
4      3         A03        2       Monitor  Pantallas    180
5      4         A05        5          <NA>       <NA>     NA
6      5         A02        4         Ratón Accesorios     12
7      5         A02        4 Ratón (nuevo) Accesorios     15
8      6         A04        1        Webcam      Vídeo     60
Warning message:
In left_join(ventas, productos_dup, by = "id_producto") :
  Detected an unexpected many-to-many relationship between `x` and `y`.
ℹ Row 1 of `x` matches multiple rows in `y`.
ℹ Row 2 of `y` matches multiple rows in `x`.
ℹ If a many-to-many relationship is expected, set `relationship =
  "many-to-many"` to silence this warning.

Si sumas las unidades de esta tabla contarías dos veces las ventas de ratones. Para reproducir el comportamiento de BUSCARV, utiliza multiple = "first" (disponible desde dplyr 1.1.0), o multiple = "last" si quieres el precio más reciente:

left_join(ventas, productos_dup, by = "id_producto", multiple = "first")

  pedido id_producto unidades producto  categoria precio
1      1         A02        3    Ratón Accesorios     12
2      2         A01        1  Teclado Accesorios     25
3      3         A03        2  Monitor  Pantallas    180
4      4         A05        5     <NA>       <NA>     NA
5      5         A02        4    Ratón Accesorios     12
6      6         A04        1   Webcam      Vídeo     60

En la práctica, una clave duplicada en una tabla de búsqueda suele ser un error. Localiza las claves repetidas con count y quédate solo con la primera fila de cada clave con duplicated o distinct antes de buscar. Tienes más opciones en cómo eliminar duplicados en R.

# ¿Qué claves están repetidas?
productos_dup %>%
  count(id_producto) %>%
  filter(n > 1)

# Quedarse con la primera fila de cada clave
productos_dup[!duplicated(productos_dup$id_producto), ]

  id_producto n
1         A02 2

  id_producto producto  categoria precio
1         A01  Teclado Accesorios     25
2         A02    Ratón Accesorios     12
3         A03  Monitor  Pantallas    180
4         A04   Webcam      Vídeo     60

Coincidencia aproximada: BUSCARV con VERDADERO

Con el último argumento a VERDADERO (TRUE en inglés), BUSCARV no busca el valor exacto: devuelve la fila del mayor valor que sea menor o igual que el valor buscado. Es la fórmula típica para tramos de impuestos, comisiones o notas. Considera la siguiente tabla de calificaciones y unos alumnos:

notas <- data.frame(nota_min = c(0, 5, 7, 9),
                    calificacion = c("Suspenso", "Aprobado", "Notable", "Sobresaliente"))

alumnos <- data.frame(alumno = c("Ana", "Ben", "Carla", "David", "Eva"),
                      nota = c(4.5, 7, 9.5, 6.9, 5))

findInterval()

findInterval(x, vec) devuelve, para cada valor de x, la posición del último elemento de vec que es menor o igual que él, que es justo lo que hace BUSCARV con VERDADERO. Igual que en Excel, la columna de búsqueda tiene que estar ordenada de forma ascendente.

findInterval(alumnos$nota, notas$nota_min)

data.frame(alumnos,
           calificacion = notas$calificacion[findInterval(alumnos$nota, notas$nota_min)])

[1] 1 3 4 2 2

  alumno nota  calificacion
1    Ana  4.5      Suspenso
2    Ben  7.0       Notable
3  Carla  9.5 Sobresaliente
4  David  6.9      Aprobado
5    Eva  5.0      Aprobado

Fíjate en que los límites se incluyen por la izquierda: un 7 exacto es “Notable” y un 6.9 es “Aprobado”, igual que en Excel.

Si un valor es menor que el primer límite, findInterval devuelve 0 y indexar con 0 elimina el elemento sin avisar en lugar de devolver NA: notas$calificacion[findInterval(c(-1, 4.5), notas$nota_min)] devuelve solo "Suspenso", un vector de longitud 1 en lugar de 2. Asegúrate de que el primer límite cubre todos los valores posibles (por ejemplo, -Inf) o usa cut, que devuelve NA.

cut()

La función cut convierte una variable numérica en intervalos (puedes verla en detalle en el tutorial sobre cómo categorizar variables en R). Para obtener los mismos intervalos que BUSCARV, pasa los límites más Inf en breaks e indica right = FALSE para que los intervalos sean cerrados por la izquierda:

cut(alumnos$nota, breaks = c(notas$nota_min, Inf),
    labels = notas$calificacion, right = FALSE)

[1] Suspenso      Notable       Sobresaliente Aprobado      Aprobado
Levels: Suspenso Aprobado Notable Sobresaliente

El resultado es un factor con las calificaciones en el orden de la tabla, lo que resulta muy práctico para tablas y gráficos.

Join aproximado con dplyr

Desde dplyr 1.1.0 también puedes hacer la coincidencia aproximada con un join, usando join_by y closest. La condición closest(nota >= nota_min) toma, para cada alumno, la nota_min más cercana que sea menor o igual que su nota:

alumnos %>%
  left_join(notas, by = join_by(closest(nota >= nota_min)))

  alumno nota nota_min  calificacion
1    Ana  4.5        0      Suspenso
2    Ben  7.0        7       Notable
3  Carla  9.5        9 Sobresaliente
4  David  6.9        5      Aprobado
5    Eva  5.0        5      Aprobado

A diferencia de BUSCARV y de findInterval, este join no necesita que la tabla de búsqueda esté ordenada.

INDICE COINCIDIR y BUSCARX en R

La combinación INDICE COINCIDIR (INDEX MATCH en inglés) es en realidad lo que has estado haciendo con match: COINCIDIR devuelve la posición e INDICE extrae el valor de esa posición. La traducción es directa:

# Excel: =INDICE(productos!D:D; COINCIDIR("Monitor"; productos!B:B; 0))
productos$precio[match("Monitor", productos$producto)]

[1] 180

Buscar hacia la izquierda

BUSCARV solo puede devolver columnas que estén a la derecha de la columna de búsqueda. Por eso se usa INDICE COINCIDIR para buscar hacia la izquierda. En R la posición de las columnas no importa en absoluto, así que puedes buscar por el nombre del producto y devolver el identificador, que es la primera columna:

productos$id_producto[match("Monitor", productos$producto)]

[1] "A03"

La búsqueda en dos direcciones INDICE(rango; COINCIDIR(fila); COINCIDIR(columna)) también es posible, buscando la fila y el nombre de la columna:

productos[match("A03", productos$id_producto), match("precio", names(productos))]

[1] 180

BUSCARX

BUSCARX (XLOOKUP) combina INDICE COINCIDIR con un valor por defecto cuando no se encuentra la clave. En R puedes escribir una pequeña función con los mismos argumentos:

buscarx <- function(valor_buscado, matriz_buscar, matriz_devuelta, si_no_se_encuentra = NA) {
  res <- matriz_devuelta[match(valor_buscado, matriz_buscar)]
  res[is.na(res)] <- si_no_se_encuentra
  res
}

buscarx(c("Ratón", "Tablet", "Webcam"), productos$producto, productos$id_producto,
        si_no_se_encuentra = "No encontrado")

[1] "A02"           "No encontrado" "A04"

Para el modo “buscar del último al primero” de BUSCARX, utiliza left_join(..., multiple = "last"), como has visto en el apartado de claves duplicadas.

Por qué tu búsqueda devuelve NA: espacios, mayúsculas y tipos de datos

Si obtienes muchos más NA de los esperados, lo más probable es que las claves parezcan iguales pero no lo sean. Considera estos pedidos escritos a mano:

pedidos <- data.frame(pedido = 1:4,
                      id_producto = c("a02", "A01 ", " A03", "A04"))

match(pedidos$id_producto, productos$id_producto)

[1] NA NA NA  4

Solo se encuentra el último pedido. El primero está en minúsculas y los otros dos tienen espacios en blanco al principio o al final. Ten en cuenta que BUSCARV no distingue entre mayúsculas y minúsculas, pero match y los joins sí: "a02" coincide con "A02" en Excel pero no en R. Limpia las claves con trimws y toupper o tolower antes de buscar:

id_limpio <- toupper(trimws(pedidos$id_producto))
id_limpio
match(id_limpio, productos$id_producto)

[1] "A02" "A01" "A03" "A04"
[1] 2 1 3 4

El otro problema clásico es el tipo de dato. match convierte ambos vectores a texto, así que el número 1 coincide con el texto "1", pero left_join se niega a unir una columna numérica con una de texto:

codigos <- data.frame(codigo = c(1, 2, 3), nombre = c("x", "y", "z"))
pedidos_2 <- data.frame(codigo = c("1", "3"))

match(pedidos_2$codigo, codigos$codigo)
left_join(pedidos_2, codigos, by = "codigo")

[1] 1 3

Error in `left_join()`:
! Can't join `x$codigo` with `y$codigo` due to incompatible types.
ℹ `x$codigo` is a <character>.
ℹ `y$codigo` is a <double>.

Convierte una de las columnas con as.character o as.numeric antes de unir. Ten cuidado con los códigos con ceros a la izquierda: si Excel guardó 001 como texto y tú tienes el número 1, no coincidirán. sprintf("%03d", 1) devuelve "001".

¿Qué método deberías usar?

La siguiente tabla resume las equivalencias entre las funciones de búsqueda de Excel y R:

Excel	R base	dplyr	Notas
`BUSCARV(x; rango; col; FALSO)`	`t$col[match(x, t$clave)]`	`left_join(df, t, by = "clave")`	Coincidencia exacta
BUSCARV de varias columnas	`df[cols] <- t[match(x, t$clave), cols]`	`left_join` + `select`
`BUSCARV(x; rango; col; VERDADERO)`	`findInterval` o `cut`	`join_by(closest(x >= clave))`	Coincidencia aproximada
`INDICE(col; COINCIDIR(x; clave; 0))`	`t$col[match(x, t$clave)]`	`left_join`	También hacia la izquierda
`BUSCARX(x; clave; col; "No encontrado")`	`match` + reemplazar `NA`	`left_join` + `coalesce`	Valor por defecto
`SI.ERROR(BUSCARV(...); 0)`	`ifelse(is.na(v), 0, v)`	`coalesce(v, 0)`
`#N/A`	`NA`	`NA`	Encuéntralos con `anti_join`

Y cómo elegir entre los tres métodos principales:

match: la forma más rápida de añadir una columna y la que se comporta exactamente como BUSCARV (primera coincidencia, mismo número de filas). No necesita ningún paquete.
left_join: la opción más legible para añadir varias columnas, mantiene el orden original de las filas y te avisa si hay claves duplicadas. Úsala si trabajas con dplyr.
merge: la alternativa de R base a left_join. Recuerda all.x = TRUE y que ordena las filas por la clave.

El BUSCARV es solo una de las fórmulas de Excel que puedes hacer en R. Tienes el resto (SUMAR.SI, CONTAR.SI, tablas dinámicas, funciones de texto…) en fórmulas de Excel en R.