# Complex Nested Lookup Fomulas - Index Match

0

"=IF(AND(VLOOKUP(Tallie1!"absent",F:F,F21(MATCH(Tallie1!C:C,P21)))..formula not working.. Looking for the following:<br/>Look for the word 'absent' in a column of worksheet2, if found, look at another column on worksheet 2 for a name, when the name is found, match the name to a column on worksheet1, when matched populate the contents of a cell from worksheet1 ..." If names are not matched then poplate nothing

Post Edited
Title: Title was not descriptive.

0

First, this is the only time that you will get help for your school work or qualification tests with an answer on here! You need to learn this stuff yourself. We are more than happy to help you learn, but, after this, you won't just be given a direct answer.

Second, this is a stupid way to get the desired result when you have to pass the spreadsheet onto someone later who might not be so great in formulas.

Third, here is the generalized version of the answer:

=IF(ISERROR(MATCH("hi",Sheet2!A1:A3,0)),"",IF(ISERROR(MATCH("name",Sheet2!D1:D3,0)),"",INDEX(E1:E12,MATCH("name",Sheet2!D1:D3,0))))

### Discussion

Used:
=TRIM(IF(ISERROR(MATCH("absent",Tallie1!F:F,0)),"",IF(ISERROR(MATCH(F379,Tallie1!C:C,0)),"",INDEX(P379,MATCH(F379,Tallie1!C:C,0)))))

It returns a blank cell
dee (rep: 2) Mar 26, '17 at 6:48 pm
Then used:
=IF(ISERROR(MATCH("absent",Tallie1!F:F,0)),"",IF(ISERROR(MATCH(F379,Tallie1!C:C,0)),"",INDEX(P379,MATCH(F379,Tallie1!C:C,0))))

Still returned a blank cell
dee (rep: 2) Mar 26, '17 at 6:50 pm
You are going to have to edit your question to include a sample workbook for anyone to be able to troubleshoot the formula.
don (rep: 1382) Mar 27, '17 at 2:04 pm
Agreed with Don on this one. This issue probably is tiny and more easily diagnosed when looking at the actual file.
cappymer1 (rep: 120) Mar 28, '17 at 5:59 am
Ok, looks like the last part of the nestled statment was not needed, so it has been resolved.
Next question though..just doing a sum of two cells, however, the format of each cell is a 'Time hh:mm:ss' and the cells are not adding. One cell is taking a total from another worksheet in the workbook eg) ='AS'!L34

Any assistance?
dee (rep: 2) Mar 28, '17 at 4:37 pm