1

My problem is very simple. Suppose I have the following table: enter image description here

and this one:

enter image description here

Is there an automatic way to obtain the following without using a sequence of nested IF?

enter image description here

1
  • Pivot tables may help
    – Dave
    Aug 4, 2015 at 11:00

3 Answers 3

5

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 .

2
  • As you use ˙FALSE˙ for third argument, result will be correct regardless of sorting. Read VLOOKUP() help: support.office.com/en-us/article/… Aug 4, 2015 at 11:43
  • @ Máté Juhász You're right. I've edited my answer to clarify it.
    – jcbermu
    Aug 4, 2015 at 11:47
2

Copy your columns A:B to J:K, and enter this formula in Column I: =VLOOKUP(J2,E:F,2)

1

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.

1
  • This method allows for future proofing. Suppose a new column is added, the VLOOKUP() will break as the column reference is static.
    – bjbk
    Aug 4, 2015 at 17:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .