CODEWITHSUNDEEP
excel
placidrain56
placidrain56

Reputation: 1

Index/Match returning wrong value

Summary tab: Picture of destination - Summary tab

Source tab: Picture of source - PFS tab

I'm having some trouble using index match combo and getting the data to reference correctly.

what I am trying to achieve is in a tab called "Summary" I have a calendar with range D5:O6. the first cell in the calendar should change everyday to reflect the current date which will change the rest of the row making a rolling calendar. I have a data reference sheet called "PFS" from which the data needs to be referenced.

The thing I'm having trouble with is that with the formula

=INDEX(PFS, MATCH(A8,Snames,0),MATCH(D5,Sdates,0))

is returning the top left intersection (B8 of the source tab) of A8 and D5 values smith, bob & 4-Aug. a cell that i have entered the value "Wrong Corner".

Snames A8:A9, Sdates D5:O5 are the rows and columns containing the dates and names from the Summary tab

what I need help with is why its returning the wrong intersection and what I can do different to get the data I'm looking for.

The correct return would be the number one, which I have conditionally formatted to turn the cell blue and blank the text

Upvotes: 0

Views: 518

Answers (1)

teylyn
teylyn

Reputation: 35990

You don't show what PFS references to. It seems to start in A1.

You can either use a range that starts in the correct cell, or add the required offset to the Match results.

=INDEX(PFS, MATCH(A8,Snames,0)+2,MATCH(D5,Sdates,0)+2)

Also make sure that Sname and Sdates refer to the ranges in the Source tab, not the Summary tab.

Upvotes: 1

Related Questions