0

If B4 matches a # in column A I need the formula to return the # in column C and then add...

For example: Since B4 matches A1, I need C1 (90) +E1 (3) + F4 (0) and return the value in C4.

I tried this, but it returns a REF. I am missing an imbedded formula somewhere. I also want the cell to be blank if there is no match. I am using Excel 2010. Updated Image Example

5
  • You say "I tried 'this' " - what is "this"? What did you try? My first guess is you used a vlookup with a range to look in that has no $ signs in, so when you copy the formula down the range does not stay fixed and it moves outside the area of your data. But that's a guess. – AdamV May 8 at 18:43
  • Must it match a value in A from a row above the current one? What if the only match is below the current row? – AdamV May 8 at 18:47
  • Hi, I am not as familiar w/ VLookup formulas & erased the one I used but it was something like =VLOOKUP($E85,$D$5:$D$137,4,FALSE), which is incorrect. I updated the rows/columns see new attached image. To clarify & for example, I am looking to return a # in the Highlighted cell in G38. (The #s in the "ID" or Col.D do not ever repeat like they do in Col. E.) To do this I need, whenever a # in E matches the corresponding # in D, for the formula to find the #'s in F & I in that same row (here F33+I33 & and add that to J38 and return the # back to G38. – user1333212 May 10 at 15:06
  • It would probably help readability if you did not use # as a shorthand for "number". – AdamV May 11 at 11:54
  • Did you try my answer? Did it help at all? – AdamV May 11 at 11:54
0

Seems like you need something like this in B2 then copied down, but your question is a little vague:

=if(countif($A$1:$A1,B2)>0,index($C$1:$C1,match(B2,$A$1:$A1,0)+index($E$1:$E1,match(B2,$A$1:$A1,0)+$F2,"")
-1

I am not as familiar w/ VLookup formulas & erased the one I used but it was something like =VLOOKUP($E85,$D$5:$D$137,4,FALSE), which is incorrect. I updated the rows/columns see new attached image. To clarify & for example, I am looking to return a # in the Highlighted cell in G38. (The #s in the "ID" or Col.D do not ever repeat like they do in Col. E.) To do this I need, whenever a # in E matches the corresponding # in D, for the formula to find the #'s in F & J in that same row (exp: F33+J33) & add that to K38 and return the # back to G38. Updated Example

2
  • This is not an answer, it belongs as a comment on the original question – AdamV May 11 at 11:53
  • Hi, thanks I cannot comment in the "comment" section w/o a 50 rep. I can only leave responses here. The tried the formula and it returns #VALUE!. Good tip regarding the "#" symbol as well. Thanks. – user1333212 May 11 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.