Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Double Lookups
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, |
#2
|
|||
|
|||
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, |
#3
|
|||
|
|||
=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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
"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 ---- |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
"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 ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unable to open Excel file by double clicks | Excel Discussion (Misc queries) | |||
Keyboard shortcut for double click in Excell cell | Excel Discussion (Misc queries) | |||
double click a xls file and start Excel but without the file | Excel Discussion (Misc queries) | |||
how do I create comma and double quote delimited file | Excel Discussion (Misc queries) | |||
Can Excel be pre-formatted to apply "double quotes" to data enter. | Excel Worksheet Functions |