0

I want to find a formula to check if the value of a cell in a column is in the range of a cell in another column and then add a value comprised of another column on the same line of the matching cell you are looking for in the range, and then to be able to duplicate this formula to all the cells in the column you are testing.

An example:

Excel example

In this pic, I want to test if the values in column B match values from column A, when it does I want the formula to check the value on the same line of column A but on column E and add it to C column on the same line as column B I am testing.

Then I want to be able to duplicate this formula on all column C.

I have this formula that I use in column C starting from C2:

=IF(COUNTIF(A2:A5,B2),E2,0)

But it does not work well and if I try to duplicate if the range changes to A3:A6, etc.

Bernhard Barker
  • 54,589
  • 14
  • 104
  • 138
Geniteur Cosmique
  • 1
  • 4
  • Adding the [tag:excel] tag since your post contained "Excel example". If that is not the tool you're using, please [edit] and update the tags appropriately. – Bernhard Barker May 28 '17 at 12:39
  • Use another column for the result, not column `C` to avoid a circular reference. – A.S.H May 28 '17 at 13:11
  • Thanks for your reply! I don't think that I would get a circular reference since C is not used in the formula, only used to desplay the result from column E – Geniteur Cosmique May 28 '17 at 14:40

2 Answers2

0

Use another column for the result, not column C, to avoid a circular reference in the formula. For example you can use column D or F If you choose column D you can use this formula in cell D2 and autofill column D with it:

D2:
=C2 + IFERROR(VLOOKUP(B2, A:E, 5, 0), 0)

As per you last comment, if you want only the matched value on column E otherwise stay blank, you can have it in column C but use:

C2:
=IFERROR(VLOOKUP(B2, A:E, 5, 0), "")
A.S.H
  • 29,101
  • 5
  • 23
  • 50
  • i think the OP wanted to concatenate (add 2 strings) not numbers, but I could be wrong – user3005775 May 28 '17 at 13:33
  • Thanks for your help! I think you are close to what I wanted to do but it's still not that. In fact I want to compare B to A. Once a cell in B contains the same string as one of the cells in A, I want the value that is on the same line as the A cell to be copied to another cell (either C or I don't care of the column) on the same line as the B cell being compared. – Geniteur Cosmique May 28 '17 at 15:04
  • @GeniteurCosmique you cannot have a formula in a cell to change "another cell". I think that my last formula fits exactly what you describe here. For example, in your example, the selected cell, with this formula, will have the value of "additive Text". On the other hand, you can try switching the roles of columns B and A in the formula if that's what you want. – A.S.H May 28 '17 at 15:15
  • @GeniteurCosmique That's good news. You're welcome. You might think of [accepting an answer](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) when it solves correctly your issue, but checking the gray checkmark (on the top-left) turning it green. Thanks. – A.S.H May 28 '17 at 15:19
0

I would use INDEX MATCH it is more efficient. And from the example it seams like you want to concatenate add one string to another not add numbers if that is the case I would do the following

=IFERROR(C2&INDEX($E:$E,MATCH($A:$A,$B2,0)),"")
user3005775
  • 306
  • 1
  • 3
  • 14
  • Thanks for your help! I think you are close to what I wanted to do but it's still not it. In fact I want to compare B to A. Once a cell in B contains the same string as one of the cells in A, I want the value that is on the same line as the A cell to be copied to another cell (either C or I don't care) on the same line as B. – Geniteur Cosmique May 28 '17 at 15:02