Vlookup Index

[cwallace70]

I have this formula =IF(COUNTIF(Sheet1!$A$2:$O$2,$A49),SUM(INDEX(Sheet1!$A$2:$O$59,0,MATCH(A49,Sheet1!$A$2:$O$59,0))))/2
that I am trying to convert to vlookup instead of Countif. I keep getting an error message when trying to convert to vlookup Is that possible to do with vlookup? and not sure what to replace the SUM or MATCH with

=IF(VLOOKUP(A3,Sheet1!$A$2:$O$2),SUM(INDEX(Sheet1!$A$2:$O$59,0,MATCH(A3,Sheet1!$A$2:$O$59,0))))

 

[jasonb75]

If it is returning 0 then it means that there is an empty cell in the result range where both criteria match.

The formula matches what you have described. Unless you post a visual example where the criteria and matching results can be clearly seen, there is nothing more that I can do.

 

[Alex Blakenburg]

There are a couple of errors in the formula. Although less common I find it visually much easier to validate the formula if all the ranges pivot off the same top left hand corner cell.
Try this:

=IFERROR(INDEX(Sheet1!$A$2:$O$59,MATCH($A3,Sheet1!$A$2:$A$59,0),MATCH(B$2,Sheet1!$A$2:$O$2,0)),"")

 

[jasonb75]

There are a couple of errors in the formula

There are no errors in the formula that I posted.

edit:- yes there are. I noticed those last night and thought I corrected them before posting.

=IFERROR(INDEX(Sheet1!$B$3:$O$59,MATCH($A3,Sheet1!$A$3:$A$59,0),MATCH(B$2,Sheet1!$B$2:$O$2,0)),"")

 

[Alex Blakenburg]

That's why although I realise using A2 is redundant this is much easier to visually validate.