3

I am trying to do an index match function in which I concatenated two text into a string and compared it to the same text in another table. Once comparing the two tables, I am able to pull a number result. However, I can't seem to figure a way to process a long string. In the score on column C it should add up all the values that are associated with the date and test.

EX:

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Sam
  • 65
  • 1
  • 1
  • 8
  • 1
    Can you please show the function you use so far? It will be easier to understand where the problem is that way. – Omer Greenwald Feb 05 '16 at 20:51
  • =INDEX($H$2:$H$10,MATCH(A2&G2,$F$2:$F$10&$G$2:$G$10,0)) This is the current function that I have right now and It works for only 1 string in Column B, but I can't figure out how to get B2 to read F2:G4 and pull out 22 as the sum. – Sam Feb 05 '16 at 20:55
  • your formula is looking for the **exact** match `test1 test 2 test 3`, which is no where to be found in *any single cell* in column G. If you want to match on any of the 3 *words* in cell B2, you're probably going to have to build a UDF. Even that will be tricky, as you have to have a way to split the words and if your sample data is anything like your real data, you don't have consistent patterns. – Scott Holtzman Feb 05 '16 at 20:59
  • I think you want something like `sumif` or `sumifs`? – findwindow Feb 05 '16 at 21:01
  • How could I solve without using the exact match function allowing me to call **test 1 test 2 test 3** in column G – Sam Feb 05 '16 at 21:03
  • Is it correct you're looking for a match in the right table for each of the concatenated values in cells in the left table? If yes, check this answer http://stackoverflow.com/a/15571252/3800705 – Omer Greenwald Feb 05 '16 at 21:03
  • What I read is that you want to verify that '22' equals the sum of the three scores that belong to the test names that are concatenated in B2. Is this correct? – Scott Craner Feb 05 '16 at 21:17
  • Yes Scott, that is exactly what I want. And those test names must be validated by the DATE and TEST #. So on 1/1/2016 I want to know the sum of test 1 test 2 test 3, I would need those values pulled from column H which correspond as test1 = 10, test = 8, test 3 = 4. Once those values are pulled the sum should go into Column C under the date of 1/1/2016 – Sam Feb 05 '16 at 21:25
  • A UDF wouldn't be hard if you were consistent with test names. Not sure how you can do this in a formulaic fashion. Excel isn't really designed to house multiple things in a cell to be processed. – Kyle Feb 05 '16 at 21:30
  • unfortunately there are more than 50 variables of " test" and there is no way for me to be consistent with it. Would a UDF be able to house all that? – Sam Feb 05 '16 at 21:33

1 Answers1

5

In your posted sample data, you have discrepancies:

  • Your example data shows the concatenated values in cell B2 to be "test1" "test 2" and "test 3"
  • However, in column G, it will only find a match for "test1" because there are no spaces for "test2" and "test3"

Assuming such discrepancies don't exist in your actual data, and that there would be exact matches because the concatenation is the result of a formula and not done by hand, you should be able to use this formula in cell C2 and copy down to get the sums you're looking for. Adjust the ranges to suit your actual data:

=SUMPRODUCT(--(COUNTIF(B2,"*"&$G$2:$G$100&"*")>0),--($F$2:$F$100=A2),$H$2:$H$100)
tigeravatar
  • 26,199
  • 5
  • 30
  • 38
  • That worked wonders! thank you, However I do have 1 more question, for some reason my first Value is not calculating correctly, column C2 in the example, but everything is calculating properly. Any ideas? Excel through a weird error saying that number cannot be found in the sheet when I try to search for it. ex: c2 says 14, but when trying to search for the number 14 it is nowhere to be found in excel even though it is clearly there. Any ideas? – Sam Feb 05 '16 at 23:23
  • "test3" and "test 3" is not the same, so you are only seeing 10+4 as the result. Fix your notation to be consistent and try again ;) – Stian Feb 08 '16 at 11:56