Identifying a value in a range of cells

[Pickygame]

Hi

Could anyone tell me why my calculation in the attached spreadsheet is returning the wrong result please?

I am trying to return a value from a table identifying the range. Sheet 2 has the formula with a lookup to prov bands

Thanks

[etaf]

a slight change to the table and you can use
=INDEX('Prov bands'!D1:D9,MATCH(Sheet2!B6,'Prov bands'!B1:B9,1))

this will look for the lowest nearest value - and so only uses column B
and will look for the lowest nearest value

[Fotis1991]

Hi

One way is using this ARRAY formula.

Please Login or Register  to view this content.

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

[martindwilson]

i'll answer the question you asked!!!!
The bit that would give C
=AND(VLOOKUP(3,'Prov bands'!A1:B8,2,0)<=B1,VLOOKUP(3,'Prov bands'!A1:C8,3,0)>B1)
the vlookup
VLOOKUP(3,'Prov bands'!A1:C8,3,0)
returns 2000 which is NOT > than B1

[Kaper]

You are checking
VLOOKUP(3,'Prov bands'!D1:F8,3,0)>B1
and 2000>2000 is false
that's why

to get what you claim is a good result use
VLOOKUP(3,'Prov bands'!D1:F8,3,0)>=B1