Hi All,
I'm struggling with the following formula and wondered if anyone could help me?
=INDEX(RebateV[Rebate],MATCH($L$1,RebateV[Commodity],0),MATCH(E2,RebateV[Plot type],0))
I have two worksheets within the same workbook
I want to return the value of my Rebate column, but the rebate is a different value if either a house or flat. MATCH(E2,RebateV[Plot type], This section looks at the cell in my table and compares the value associated and returns this number
MATCH($L$1,RebateV[Commodity], Looks up the header in my table and compares it with the column data in my rebate table (RebateV).
The formula appears to work fine when the reference refers to a "house" but when the cell E2 refers to a "flat" it returns a #REF! error and I've no idea why.
So to summarise I have two reference conditions to return a value in my rebate column?
I've attached 2 images with my problem changing and hiding some of the columns and data.