Thread: Lookup with 2 different criterias

Hi

I'm sure there is a very simple answer to my lookup problem but I can't seem to figure it out so hoping someone can help.

I want to be able to do one lookup to search for a date which I can do but I need a second part of the formula to check that date to see if the cell is above zero.

For example, in the spreadsheet attached I want to be able to search in the sheet template 1, Cell C1.

I want to lookup the date in cell B2 in the sheet project 1 cells h to t. Then once it has found that date in the row i want to search that column to see if the cell is above 0.

For January I want to be able to search the column H and in H5 be able to give me the value in cell A5.

I guess what I'm looking for is to search the row (row 4) with the dates in and then search that column for a particular cell which will give me the name of that person (column A).

I'm sure I could have explained it better but the spreadsheet is attached if someone can help me.

No attachment yet...

Do you mean:

=INDEX('Project 1'!$H$5:$T$21,ROWS($A$1:$A1),MATCH($B$2,'Project 1'!$H$4:$AR$4,0))

copied down.

If yes, you can also change the 'Project 1' so you can indirectly look at appropriate sheet, and use one formula copied down and across.

e.g.

=INDEX(INDIRECT("'"&C$2&"'!$H$5:$T$21"),ROWS($A$1:$A1),MATCH($B$2,INDIRECT("'"&C$2&"'!$H$4:$AR$4"),0 ))

copied down and across.

Sorry I probably didn't explain it very well. The answer will come from column A in the project 1 sheet.

I need to search the date from the template sheet in cell B2 against rows H5 to T5 in project 1.

Once I know the date I want it to search a particular cell in the project sheet to see if it is above zero. If it is then the answer will come from column a in the project sheet.

For example if i was inputting the formula in the template sheet in cell C3 I would want to get my answer for january (column H)from cell A5

If I was inputting the formula in the template sheet in cell C14 I would want to get my answer for March (Column J) from cell A16.

I hope that makes things clearer.

Is this it?

=IF(INDEX('Project 1'!$H$5:$T$21,ROWS($A$1:$A1),MATCH($B$2,'Project 1'!$H$4:$AR$4,0))>0,'Project 1'!$A5,"")