Excel 2003 - OFFSET/MATCH Problem... HELP!!!

Soldato
Joined
27 Sep 2005
Posts
5,909
Location
Burbage, Hinckley
I'm pulling my hair out trying to fix an error in an excel doc I am working on.

Please open document from here:-

http://www.4shared.com/document/ghUjM1or/comms.html

I have cell B76 retreiving a value from a sub table using HLOOKUP. The cells in the sub table in turn retreive a value from further tables using OFFSET/MATCH.

All works absolutely fine except when I choose a certain value in cell B76... If I select 'High' in B76 then I only get an error message returned - I can see no reason why it's returning an error with this one value selected though!

hope that was clear - if not then just open up the doc above and try changing the value in B76 between, Low, Medium, High and Very High - you will then see that the value High returns an error and be able to trace this back to the tables the value is retreived from.

Any help really appreciated - thanks.
 
You need to define the match type as 'exact' to your formula in D76:Q76 and D78:Q78. As an example, D76 should read:

=OFFSET(A187,MATCH($B$76,A188:A191,0),MATCH($B$78,$B187:$AZ187,0))

The bits in bold are the part you've missed so Excel returns an error as it can't choose whether to use 'High' or 'Very High' as the lookup :)
 
Last edited:
Hi I just tried your workbook. I modified the formula in D78 when picking the following worked.

New formula =OFFSET(A89,MATCH(B76,A90:A93,0),MATCH(B78,B89:AZ89,0))

I only tested it for the settings below, (Added exact match)

Data = COBRA HOT
Utility = ELEC
Cust Value = Medium
Site Value = High
Term = 1 yr
Notch = 0
Offer = YES
DD = YES


Like I said please test with other options as this was just a quick thing
 
You need to define the match type as 'exact' to your formula in D76:Q76 and D78:Q78. As an example, D76 should read:

=OFFSET(A187,MATCH($B$76,A188:A191,0),MATCH($B$78,$B187:$AZ187,0))

The bits in bold are the part you've missed so Excel returns an error as it can't choose whether to use 'High' or 'Very High' as the lookup :)

Hi I just tried your workbook. I modified the formula in D78 when picking the following worked.

New formula =OFFSET(A89,MATCH(B76,A90:A93,0),MATCH(B78,B89:AZ89,0))

I only tested it for the settings below, (Added exact match)

Data = COBRA HOT
Utility = ELEC
Cust Value = Medium
Site Value = High
Term = 1 yr
Notch = 0
Offer = YES
DD = YES


Like I said please test with other options as this was just a quick thing

Of course - makes perfect sense now when i think about it :)

Thanks everyone.
 
Back
Top Bottom