Evaluate excel formula

Copper Contributor

Hello, I wanted to ask related to If function's evaluation. 




In screenshot 1, for cell A10, I evaluated the formula and though the other sheet - all questions has a range of questions, why does it only pick only one (which is already on A10) value and returns false. 

My aim was to understand how A10 is appearing in question explorer. 

I have included the file link as well for your reference for sheets - Question Explorer and all_questions.  Transport File Link

Please guide me through this.  


1 Reply


The formulas in A10:A25 search all_questions!A2:A417 for the text in cell A6:

"Factors encouraging ultra-low emission car purchase or lease: safety features or record"

This text is found in all_questions!A87:A89.

The formula in A10 simply returns the value of the first matching cell, which is - not surprisingly - the same as the value of A6.

But the formulas in A11 to A25 return the values of the cells in all_questions!D2:D417 that have the value of A6 in column A:

A11 returns the value of all_questions D87.

A12 returns the value of all_questions D88.

A13 returns the value of all_questions D89.

A14 and down return an empty string because there are no more matches.