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,

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

"Philippe L. Balmanno" wrote in message
news:u2Akd.43572$SW3.34348@fed1read01...
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,

=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 that
number of rows
3) find the position of the exact l54 value in b5:al5 and go up that
number of columns

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

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


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=277267

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.

"duane" wrote in message
...

=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 that
number of rows
3) find the position of the exact l54 value in b5:al5 and go up that
number of columns

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

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


--
duane


------------------------------------------------------------------------
duane's Profile:
http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=277267

Hi

so is the problem solved now?

Cheers
JulieD

"Philippe L. Balmanno" wrote in message
news:UOAkd.43618$SW3.10629@fed1read01...
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.

"duane" wrote in message
...

=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 that
number of rows
3) find the position of the exact l54 value in b5:al5 and go up that
number of columns

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

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


--
duane


------------------------------------------------------------------------
duane's Profile:
http://www.excelforum.com/member.php...o&userid=11624
View this thread:
http://www.excelforum.com/showthread...hreadid=277267

"Philippe L. Balmanno" wrote
....
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))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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" wrote in message
...
Hi

so is the problem solved now?

Cheers
JulieD

"Philippe L. Balmanno" wrote in message
news:UOAkd.43618$SW3.10629@fed1read01...
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.

"duane" wrote in message
...

=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 that
number of rows
3) find the position of the exact l54 value in b5:al5 and go up that
number of columns

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

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


--
duane


------------------------------------------------------------------------
duane's Profile:
http://www.excelforum.com/member.php...o&userid=11624
View this thread:
http://www.excelforum.com/showthread...hreadid=277267

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

Hi Philippe

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

Cheers
JulieD

"Philippe L. Balmanno" wrote in message
news:XUBkd.43659$SW3.18011@fed1read01...
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" wrote in message
...
Hi

so is the problem solved now?

Cheers
JulieD

"Philippe L. Balmanno" wrote in message
news:UOAkd.43618$SW3.10629@fed1read01...
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.

"duane" wrote in message
...

=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 that
number of rows
3) find the position of the exact l54 value in b5:al5 and go up that
number of columns

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

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


--
duane


------------------------------------------------------------------------
duane's Profile:
http://www.excelforum.com/member.php...o&userid=11624
View this thread:
http://www.excelforum.com/showthread...hreadid=277267

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"JulieD" wrote in message
...
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

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" wrote in message
...
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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"JulieD" wrote in message
...
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

"Philippe L. Balmanno" wrote
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

The coords were approx, pre-GPS days <g

Isn't GPS a neat tool ..

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----