Problem with Indirect function in HLOOKUP

1. Problem with Indirect function in HLOOKUP

I can get two formulas to correctly display a value, but when I try to combine the formulas for simplicity I get a "The formula you typed contains an error" error message. The "incorrect" formula (two combined) is:

=HLOOKUP(INDIRECT("'Eq. List-to-P&ID template'!"&CHAR(65+MATCH(LEFT(IF(NOT(LEFT(RIGHT(E3,6),1)="-"),RIGHT(E3,6),RIGHT(E3,5)),2)&"xxx",'Eq. List-to-P&ID template'!\$B28:\$K28,0))&"30"),'Eq List 5.11.12'!\$A\$2:\$CV\$598,MATCH(E3,'Eq List 5.11.12'!\$A2:\$A598,0),0)

, and it should display "406 GAL", but I get the error message.

The formula broken into two chunks works:

=INDIRECT("'Eq. List-to-P&ID template'!"&CHAR(65+MATCH(LEFT(IF(NOT(LEFT(RIGHT(E3,6),1)="-"),RIGHT(E3,6),RIGHT(E3,5)),2)&"xxx",'Eq. List-to-P&ID template'!\$B28:\$K28,0))&"30") gives me "Capacity" in cell E34, then

=HLOOKUP(E34,'Eq List 5.11.12'!\$A\$2:\$CV\$598,MATCH(E3,'Eq List 5.11.12'!\$A2:\$A598,0),0) displays "406 GAL" as it should.

So the problem is with combining the two. When I replace the INDIRECT(...) statement in for E34 in the HLOOKUP, I get the error statement. I know this is difficult to track a bug without having the spreadsheet, but does anybody know why this might be happening? All I'm doing is replacing values that already work!

Thanks guys!

2. Re: Problem with Indirect function in HLOOKUP

This worked for me:

``Please Login or Register  to view this content.``

3. Re: Problem with Indirect function in HLOOKUP

NBVC,

Your formula contains '[Eq List 5.11.12]Eq List 5.11'! at some places, which happens when Excel can't find the sheet you're looking for (I'm sure you knew that). Either way, when I cleaned that up and threw it back into Excel, it was the exact same code that I had and it still gave me the error. Do you think it might be a software version issue?

4. Re: Problem with Indirect function in HLOOKUP

I cleaned those out and it still did not error out... as in no pop up error message boxes...

``Please Login or Register  to view this content.``
well, I get #N/A but I have no data...

5. Re: Problem with Indirect function in HLOOKUP

why don't you post a sample workbook?

6. Re: Problem with Indirect function in HLOOKUP

Sample of Problem.xls

Check out the attachment. Let me know if you get the error or not

7. Re: Problem with Indirect function in HLOOKUP

It's a very dumbed down version of the workbook for confidentiality reasons, but it still gives the same headache.

8. Re: Problem with Indirect function in HLOOKUP

Yes, the error you are getting is because you are only allowed up to 7 nested functions in Excel (prior to Excel 2007), doing that extra nesting takes you over the limit....

9. Re: Problem with Indirect function in HLOOKUP

UGHHHHHH, that's what I feared. Good to know work cares enough about the future to keep our software in the past. I appreciate the quick responses to keep me from losing it! I'll just have to go two-celled I suppose. Thanks everybody!

10. Re: Problem with Indirect function in HLOOKUP

One minor change in your formula, makes it work....

``Please Login or Register  to view this content.``

---------- Post added at 04:30 PM ---------- Previous post was at 04:29 PM ----------

I changed NOT(LEFT(RIGHT(C3,6),1)="-") to LEFT(RIGHT(C3,6),1)<>"-" ... this loses just enough nested functions...

11. Re: Problem with Indirect function in HLOOKUP

THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU!!!! Something so simple. Even if I had known the nest rule, I wouldn't have caught that. A million thanks, NBVC!

@ lilaustino

Thanks.

13. Re: Problem with Indirect function in HLOOKUP

Sorry about that, Cutter. I'm a rook, and I won't make the mistake again. This forum was very useful, so I'll def be back with more questions. Thanks again!

14. Re: Problem with Indirect function in HLOOKUP

what a large black bird that nests in groups?

There are currently 1 users browsing this thread. (0 members and 1 guests)