# 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!

12. ## Re: Problem with Indirect function in HLOOKUP

@ lilaustino

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
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)