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