Double Lookups

[Philippe L. Balmanno]

Excel 2002:
Help please, I'm trying to do a double lookup. I have two sheets labeled
"CERS" and "UAS Compound Factor". CERS will have two values to lookup in
the UAS Compound Factor sheet; one a value in a row and the other a value in
a column. The problem is that the formula returns an #N/A error when there
is a value in the cell that is cross referenced.

I'm using a formula similar to Chip Pearsons example at:
http://www.cpearson.com/excel/lookups.htm

My formula is:
=OFFSET('UAS Compound Factor'!A5,MATCH(CERS!L53,'UAS Compound
Factor'!A6:A105,0),MATCH(CERS!L54,'UAS Compound Factor'!B5:AL5,0))

UAS Compound Factor'!A5 = n -- Base Cell
CERS!L53 = 54 -- n value to look up
UAS Compound Factor'!A6:A105,0 -- brings back cell A59 where 54 is found
CERS!L54 = 1.5% -- i value to lookup
'UAS Compound Factor'!B5:AL5,0 -- brings back cell D5 where 1.5% is found

The expected value should be the intersection of A59 and D5 which is 82.295
instead I get a #N/A.

UAS Compound Factor = Uniformed Annual Series Compound Factor = [(1+i)n-1]/i

Thanks in advance,

 

[JulieD]

Hi Philippe

i've set up a workbook based on your formula and it works fine for me ...
the only thing i can suggest is that you click on the cell containing the
formula and use tools / formula auditing and evaluate formula to step
through the formula to see where the problem is occuring.

Cheers
JulieD

 

[duane]

=OFFSET('UAS Compound Factor'!A5,MATCH(CERS!L53,'UAS Compound
Factor'!A6:A105,0),MATCH(CERS!L54,'UAS Compound Factor'!B5:AL5,0))

isn't this formula finding the value in a cell defined as

1) start at a5
2) find the position of the exact l53 value in a6:a105 and go down tha
number of rows
3) find the position of the exact l54 value in b5:al5 and go up tha
number of columns

presumably the value of l54 exists in a6:a105 and l53 exists i
b5:al5?

Is a coincidence that you expect the value of 54 to be in row 59 (54
than 5)

 

[Philippe L. Balmanno]

In this case the array starts at A5 (5+54=59) therefore A59 is the row I
need.

The error is in the column value. It was a product of a formula
=1.25%+.25%=1.5%. The Offset Match formula doesn't like looking for the
product of a formula but rather a value because it determined that this
product can change if another factor is changed. Once I entered 1.5% in the
cell my Offset formula worked.

 

[JulieD]

Hi

so is the problem solved now?

Cheers
JulieD

In this case the array starts at A5 (5+54=59) therefore A59 is the row I
need.

The error is in the column value. It was a product of a formula
=1.25%+.25%=1.5%. The Offset Match formula doesn't like looking for the
product of a formula but rather a value because it determined that this
product can change if another factor is changed. Once I entered 1.5% in
the cell my Offset formula worked.

 

[Max]

The error is in the column value. It was a product of a formula
=1.25%+.25%=1.5%. The Offset Match formula doesn't like looking for the
product of a formula but rather a value because it determined that this
product can change if another factor is changed. Once I entered 1.5% in the
cell my Offset formula worked.

Think it's because the MATCH() with "zero" as match type
is looking for an exact match. As the lookup value is a product of
a formula, the calculated value may not exactly match
that in the lookup col/row.

Try using ROUND() on the lookup value to improve matching,
viz.: use ROUND(CERS!L53,4) and ROUND(CERS!L54,4)

So this might work:

=OFFSET('UAS Compound Factor'!A5,MATCH(ROUND(CERS!L53,4),'UAS Compound
Factor'!A6:A105,0),MATCH(ROUND(CERS!L54,4),'UAS Compound Factor'!B5:AL5,0))

 

[Philippe L. Balmanno]

Yes, thanks.

Using the evaluate formula tool, It complained that there was a formula that
could change when the sheet was recalculated. I looked at the column
headings as I knew I used a formula to get those values and determined that
the problem was that my 1.5% (column heading) was the problem. My column
heading (1.5%) is a product of a formula and the offset match formula
prefers the actual value rather than the product of a formula. Once I fixed
this the formula worked. I will be trying out Max's suggestion although
rounding isn't the problem as the product is a solid value of 1.5% but this
may lead to a possible way to use a formula's product in the match.

 

[JulieD]

Hi Max

do you know how long it took me to find out you were in singapore .. you
think there would be lots of long & lat calculators on the web wouldn't you!

Cheers
JulieD
31:50:00S 116:10:00E

 

[JulieD]

Hi Philippe

glad its solved .. IMHO the evaluate formula tool is one of the most useful
things in excel.

Cheers
JulieD

 

[Max]

You didn't use Excel? <bg>

Something like
: = VLOOKUP(LatnLong,WorldCityLatnLong,2,0)
would have returned the result immediately, hah!

Sorry, didn't mean to make it tough to unravel
but sometimes good to retain some mystique, eh? <g>

 

[Philippe L. Balmanno]

I thought Sinapore was at GMT 8, 1° 16' N 103° 51' E.
I know you're 5 minutes north and 6 minutes east of the center of
Sinagapore. Isn't GPS a neat tool, I use it a lot in canyons here. GMT -8,
32° 42' N 117° 9' W

 

[Max]

I thought Singapore was at GMT 8, 1° 16' N 103° 51' E.
I know you're 5 minutes north and 6 minutes east of the center of
Singapore. Isn't GPS a neat tool, I use it a lot in canyons here. GMT -8,
32° 42' N 117° 9' W

Isn't GPS a neat tool ..

yes .. here's looking at where you are
(from 1 million km above)
: http://tinyurl.com/6u2kx