Index/Match returning wrong value

Question

Summary tab:

Source 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

If Snames is A8:A9, then MATCH(A8,Snames,0) will return 1. If Sdates is D5:O5 then MATCH(D5,Sdates,0) will return 1. =INDEX(PFS, 1,1) will therefore return the first cell of the range PFS. What is the range PFS? — GSerg
– GSerg, Commented Aug 4, 2021 at 20:07

teylyn · Accepted Answer · 2021-08-04 20:08:00Z

1

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.

answered Aug 4, 2021 at 20:08

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

placidrain56 Over a year ago

That certainly fixes my issue with the references and returns the correct value.

teylyn Over a year ago

@placidrain56 If that resolved your issue, please mark the answer as described in the tour. If it didn't, please leave a comment, so I can follow up.

Collectives™ on Stack Overflow

Index/Match returning wrong value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related