Hope someone can rescue me here...I am in the homestretch of an exciting
(at least to me) spreadsheet that involves multiple Rankings, Tables,
Lookups, etc.

Here is the sticking point: I need to do a DOUBLE LOOKUP in a table,
and have thoroughly read and applied Chip Pearson’s excellent examples
on Double Lookups on his website.

I believe my problem stems from the fact that I am referencing the two
required reference cells from cells that they themselves are calculating
a Lookup, so, in essence, it is a short chain of Lookups. The first
Lookup works great, and shows the proper Values in the cells for the
output. The second Lookup via the Pearson method references these two
cells for the output, but returns #N/A. Reading through the Excel
documentation leads me to believe that the problem is that it is
referencing something that also must lookup something, kind of like a
Loop or something?

In any event, I have tried everything to the extent of my Excel
knowledge, using Value to pass the number over to another cell, etc.
Nothing has worked so far. I have even named the tables in case that
might have something to do with it as well...it didn’t...LOL.

I tried manually typing in the outputted numbers from the first lookup
and then set up the second Lookup to reference these Values instead of
the cell that does a lookup and it worked perfectly. I therefore feel
the problem is as I mentioned before, a Lookup referencing another
previous Lookup cell. If this can be done without VB, and simply
through formulas, I would truly be grateful!

Example code follows:

This is the 1st Lookup...
=IF(A5="",0,IF(M5=0,0.01,VLOOKUP(I5,PF,2)))
--------outputs 0.26

This is the 2nd Lookup...
=OFFSET($U$1,MATCH(O$20,TPF,0),MATCH(O5,IPF,0))
--------outputs #N/A

PF is the first-named Table, TPF is the first Column in the 2nd Table,
and IPF is the first Row in the 2nd Table, with $U$1 being the anchor
point for that 2nd table (no data).



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

select the cell with the double lookup formula

highlight the O$20 in the formula bar, hit F9 - what value does it show

hit esc
Select MATCH(O$20,TPF,0) in the formula bar - what does it show
hit esc

work through the various parts of your formulas and see if you can find the
problem. #N/A indicates the value being looked up has not been found. But
I don't see anything in your description that would indicate this is a
processing or systemic problem with excel. I would be more inclined to
believe that something in your formula is incorrect.

--
Regards,
Tom Ogilvy


"Sandy Pasdak" wrote in message
...
Hope someone can rescue me here...I am in the homestretch of an exciting
(at least to me) spreadsheet that involves multiple Rankings, Tables,
Lookups, etc.

Here is the sticking point: I need to do a DOUBLE LOOKUP in a table,
and have thoroughly read and applied Chip Pearson's excellent examples
on Double Lookups on his website.

I believe my problem stems from the fact that I am referencing the two
required reference cells from cells that they themselves are calculating
a Lookup, so, in essence, it is a short chain of Lookups. The first
Lookup works great, and shows the proper Values in the cells for the
output. The second Lookup via the Pearson method references these two
cells for the output, but returns #N/A. Reading through the Excel
documentation leads me to believe that the problem is that it is
referencing something that also must lookup something, kind of like a
Loop or something?

In any event, I have tried everything to the extent of my Excel
knowledge, using Value to pass the number over to another cell, etc.
Nothing has worked so far. I have even named the tables in case that
might have something to do with it as well...it didn't...LOL.

I tried manually typing in the outputted numbers from the first lookup
and then set up the second Lookup to reference these Values instead of
the cell that does a lookup and it worked perfectly. I therefore feel
the problem is as I mentioned before, a Lookup referencing another
previous Lookup cell. If this can be done without VB, and simply
through formulas, I would truly be grateful!

Example code follows:

This is the 1st Lookup...
=IF(A5="",0,IF(M5=0,0.01,VLOOKUP(I5,PF,2)))
--------outputs 0.26

This is the 2nd Lookup...
=OFFSET($U$1,MATCH(O$20,TPF,0),MATCH(O5,IPF,0))
--------outputs #N/A

PF is the first-named Table, TPF is the first Column in the 2nd Table,
and IPF is the first Row in the 2nd Table, with $U$1 being the anchor
point for that 2nd table (no data).



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks Tom!

Your info was great...I never knew about the F9 check. That is a very
handy feature. You were right - I checked each component and the
culprit was the MATCH(O$20,TPF,0)being set to Zero instead of "1."

Thanks again...the spreadsheet rocks now!

Sandy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!