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

Joebob · 19 Apr 2011 at 13:26

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.

spydermixx · 19 Apr 2011 at 20:47

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

reilly · 19 Apr 2011 at 20:49

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

spydermixx · 19 Apr 2011 at 20:53

Lol, the OP waits hours for a reply then two come along in two minutes!

reilly · 19 Apr 2011 at 20:54

Well when you have to do this all day at work you have to have a break before getting 'stuck in again'

spydermixx · 19 Apr 2011 at 20:55

Too true

Joebob · 19 Apr 2011 at 20:57

spydermixx said:
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

reilly said:
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.