V Look Up / Index Match

[Tingle]

Hi All,

I have two spreadsheets for a monthly mail shot. The first spreadsheet has Full Names and Dates of births. The Second spreadsheet is a list of people that have returned with a response. Full Name & DoB again.

I need to do the following....

• Run a look up to see who and how many have responded from the first list.
• Identify those who have not responded
• Show both these results in numbers (Maybe a Count/Sum formula)

Any help would be much appreciated. I am assuming that I need to run a look up form the original data, which then gives an outcome of "Yes or No", then a count or sum function to show numbers?

Thanks

Tingle

 

[Rickmaurinus]

Hi Tingle,

I would try a combination of VLOOKUP and IF.

=IF( ISERROR( VLOOKUP( Lookupvalue;array;column;truefalse) ; 0; 1)

If the VLOOKUP formula does not have a result, it will return a 0, if it returns a correct result it will give you a 1.
Then you can use a SUM formula to add up how many people have responded.
All the people showing a 0 have not responded.

Good luck!
Rick