Match and index formula

[pkam2010]

HI

i have a formula of

={IFERROR(INDEX(sheet2!$A$2:$H$500,MATCH(2,(sheet2!$G$2:$G$500=sheet1!$B$3)*(sheet2!$H$2:$H$500=sheet1!$AB$2),0),4)," ")}

A3 is the date
B3 is the name

so it is matching the date and name of a person and returning the data. want it to match that and then look at the row below

can someone help

 

[Special-K99]

Re: Help match and index formula

Isn't this just

={IFERROR(INDEX(sheet2!$A$2:$H$500,MATCH(2,(sheet2!$G$2:$G$500=sheet1!$B$3)*(sheet2!$H$2:$H$500=sheet1!$AB$2),0)+1,4)," ")}

 

[steve the fish]

Re: Help match and index formula

This:

(sheet2!$G$2:$G$500=sheet1!$B$3)*(sheet2!$H$2:$H$500=sheet1!$AB$2)

can never produce 2 so the MATCH will always fail. It can only produce array of 0s and 1s.

 

[pkam2010]

Re: Help match and index formula

thats what i thought but it doesn't work

 

[Special-K99]

Re: Help match and index formula

Try

={IFERROR(INDEX(sheet2!$A$2:$H$500,MATCH(1,(sheet2!$G$2:$G$500=sheet1!$B$3)*(sheet2!$H$2:$H$500=sheet1!$AB$2),0)+1,4)," ")}
Array formula, use Ctrl-Shift-Enter

 

[pkam2010]

Re: Help match and index formula

thanks thats worked

 

[pkam2010]

Re: Help match and index formula

Hi

i need to expend the search to find also whats in cell b7 and look what is sheet2 but only contain the first for numbers. is this possible