Inconsistent Index/Match formula

[chilli76]

Hi all

I have adapted an Index/Match formula from an open office workbook I previously used and in the main it works and returns the correct values. I have noticed though it's not consistent and some values are not the right ones. I have absolutely no idea why this is the case and could really do with some expert help.

I am sure it will be me who has gone wrong somewhere but I just can't work out why it works for some and not others.

The formula is

=INDEX(Prices!$C$2:$C900,MATCH(VLOOKUP(AY18,Prices!$G$2:$I$900,2,0),Prices!$C$2:$C900,0)+2)

but I've attached a sample sheet to put it in some context.

BG17 in 'My Odds' sheet is correct returning the correct value from C22 in 'Prices' sheet

BUT

BG18 in 'My Odds' sheet is wrong returning the incorrect value from 'Prices' sheet. It should be returning C202 but it isn't.

Any help much appreciated

[Pete_UK]

In the example, Queens Park Rangers in AY18 returns a VLOOKUP value of 3.10, and your formula then tries to match that to values in column C. However, there are several values of 3.10 in column C, so the formula matches with the first of these (in cell C24) and thus returns the value from 2 cells below this.

You need to change the formula in BG18 to this:

=INDEX(Prices!$C$2:$C900,MATCH(VLOOKUP(AY18,Prices!$G$2:$I$900,3,0),Prices!$B$2:$B900,0)+2)

so that you return QPR from the VLOOKUP and then try to match that with column B.

Other formulae in column BG will also need to be changed.

Hope this helps.

Pete

[chilli76]

ahhhh I see. I could not for the life of me see what it was doing. Makes complete sense now

Many thanks, I'd have been at that for days.

[protonLeah]

Named ranges:
football_prices =Prices!$A$2:INDEX(Prices!$A:$A,COUNTA(Prices!$A:$A))
MarketTeamPrice =Prices!$A$2:INDEX(Prices!$D:$D,COUNTA(Prices!$A:$A))
TeamName =Prices!$B$2:INDEX(Prices!$B:$B,COUNTA(Prices!$A:$A))
Tissue_price_Betfair =Prices!$G$2:INDEX(Prices!$I:$I,COUNTA(Prices!$I:$I))

BG17:

Please Login or Register  to view this content.