My problem is very simple. Suppose I have the following table:
and this one:
Is there an automatic way to obtain the following without using a sequence of nested IF?
Let's suppose your first table is in Sheet1
and the second on Sheet2
, then on cell C2 of Sheet1
you have to put this formula:
=VLOOKUP(A2,Sheet2!$A$2:$B$4,2,FALSE)
It will take the value on A2
(1), look for a matching value in the range Sheet2!$A$2:$B$4
, and if there is a match, insert the value on column 2
(cat). The FALSE
indicates that it must be an exact match .
VLOOKUP()
help: support.office.com/en-us/article/…
Aug 4, 2015 at 11:43
Copy your columns A:B to J:K, and enter this formula in Column I: =VLOOKUP(J2,E:F,2)
Another option is to use INDEX and MATCH. Enter this formula in C2, and drag down.
=INDEX($F$2:$F$4,MATCH($A2,$E$2:$E$4,0))
Explanation:
MATCH($A2,$E$2:$E$4,0)
Look up the range E2:E4 (values = 1, 2, 3), and find the cell that matches A2 (value = 1). This will return the value 1, since A2 matches the 1st value in the range.
=INDEX($F$2:$F$4,1)
In the range F2:F4 (values = cat, dog, and house), take the 1st value, i.e. cat.