INDEX, MATCH EVERY OTHER ROW

Hi working fairly new to working with the Index/Match function.  I have a worksheet that I would like to return the second row of data and also be able to drag the formula down and across to populate the data.

Attaching spreadsheet in starting with column Q to AN on the Report would like to match based on column b (store #) and return the date from the same week (columns N-AK) on the PUC tab that would be in the row where the Actual Receipt Date is.

So on my attempt 3/4/13 was returned, looking for 2/27/13.
EE-Index-Match.xlsx
jmac001Asked:
Who is Participating?
 
mvidasConnect With a Mentor Commented:
jmac,

Your N11:AK11 range will only return data from there. I assume you want it to be N11:AK152 as your other range has it. Your MATCH function will return the relative row number in that range, so just add +1 to it. Then you can specify a column number to make sure to retrieve the applicable week. Put this in Q2:
=IF(ISBLANK(INDEX(PUC!$N$11:$AK$152,MATCH(Report!$B2,PUC!$B$11:$B$152,0)+1,COLUMN()-16)),"",INDEX(PUC!$N$11:$AK$152,MATCH(Report!$B2,PUC!$B$11:$B$152,0)+1,COLUMN()-16))

Open in new window

Matt
0
 
NBVCCommented:
Try changing the INDEX range to one row below...

=IF(ISBLANK(INDEX(PUC!$N12:$AK12,MATCH(Report!$B2,PUC!$B$11:$B$152,0))),"",INDEX(PUC!$N12:$AK12,MATCH(Report!$B2,PUC!$B$11:$B$152,0)))
0
 
jmac001Author Commented:
Thank you Matt that is what I was looking for, I was playing with the columns to see what result I would get back and forgot to change it back.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.