Compare two values in an array and if they match in a row, return a value from the third column in the same row.

I am hoping someone can help me solve this specific problem. I understand it may have something to do with VLOOKUP, or MATCH and INDEX and array formulas but I cannot quite get the right ccomination of things to work.

I have a table containing names in column 1, text values in column 2, and totals in column 3.





























  A B C
1 Name Text Value Total
2 a Text 1 12
3 a Text 2 4
4 a Text 5 8
5 b Text 2 15
6 b Text 3 3
7 c Text 2 4
8 c Text 3 2
9 c Text 4 1
10 c Text 5 9
11 d Text 1 5
12 d Text 3 5
13 e Text 1 3
14 e Text 2 2
15 e Text 3 8
16 e Text 4 4
17 e Text 5 6
18 f Text 1 4
19 f Text 2 4
20 f Text 3 5
21 f Text 5 3
22 g Text 2 1
23 g Text 3 2
24 g Text 5 4

I am trying to create a formula that takes a name from a list of individual names and checks the above table to see if a row contains both the name in column a and a specified text in column b. If both values are present in the row I want the formula to return the value in column c.

I want to use the formula to populate the table below:













  E F G H
1 Name Text 1 Text 2 Text 4
2 a      
3 b      
4 c      
5 d      
6 e      
7 f      
8 g      

So, the formula in F2 should use the name from E2 which is “a”. It should then find instances of “a” in A2:A24, and if B2:B24 also has “Text 1” in the same row the formula should return the value from column C in the same row. If the formula is possible it should return the value 12 in cell F2.

I would then use the an adjusted version of this formula to check for names

I would really appreciate someone’s help with this. It’s probably simple, but I just cannot crack it despite a lot of searching online!!

 

By: Rob Sadler

Leave a Reply

Your email address will not be published. Required fields are marked *