Check if value contains in another column and then get value based on that (Excel)

Question

I have three columns in Excel sheet. For example:

Now what I want to achieve is that when ID in D column matches ID in A column, then VALUE in B column is copied to E column next to matching ID.

For example: ID 4 (in D column) should have value 11 (in E column [E2]) ID 7 (in D column) should have value 77 (in E column [E6])

Have a look at [this](https://stackoverflow.com/a/58436658/9758194). Get in the habit of using `INDEX` and `MATCH` functions for such tasks. — JvdV, Dec 12 '19 at 12:13

score 1 · Accepted Answer · answered Dec 12 '19 at 12:19

1

Use INDEX and MATCH. Type this formula into E2 and drag it down:

=INDEX($B$2:$B$9,MATCH(D2,$A$2:$A$9,0))

MATCH will look up the row number in column A and INDEX delivers the corresponding value from column B.

answered Dec 12 '19 at 12:19

Michael Wycisk

score 1 · Answer 2 · answered Dec 12 '19 at 12:20

1

So I solved this. I used the following formula in E cell:

=LOOKUP(D2,$A$2:$A$9,$B$2:$B$9)

and then I dragged this all the way to the end of E column (E9)

answered Dec 12 '19 at 12:20

Omaja7

Be aware that your lookup vector must be sorted with the `LOOKUP` function. Further, if the value from column D is not in column A, `LOOKUP` will find the next-smallest value. It will work in your example, but might be a source of frustration when your data changes. – Michael Wycisk Dec 12 '19 at 12:28
And you are absolutely right. When I examined my data (this question contains few rows but actually I had over 10 000 rows), then I saw some invalid results. But your answer made them correct. Sorry, my knowledge about Excel is very limited. Therefore I accept your answer. Thank you for your time. – Omaja7 Dec 12 '19 at 12:33
Glad it helped :-) – Michael Wycisk Dec 12 '19 at 12:36

score 0 · Answer 3 · answered Dec 12 '19 at 13:19

0

You could use the below:

=IFERROR(VLOOKUP(D2,$A$2:$B$9,2,0),"Value not found")

In case of missing value you will get Value not found

answered Dec 12 '19 at 13:19

Error 1004

3 Answers3