Nesting VLOOKUP and HLOOKUP (HOW TO DO IT???)

[Box]

Back in the day, I used to be a Q-Pro wiz. I knew how to do it all. Then windows replaced dos, and Excel replaced Quattro Pro. Now I need to perform one of those old tricks, but I can't get the syntax just right, and maybe someone can help me.

I want to nest an HLOOKUP inside a VLOOKUP. I could do it in Q-Pro. My Q-Pro routine used to look down the first column of a list, and find a value that was <= a thread diameter. Then the HLOOKup would look across that particular row, and find the corresponding thread pitch range. This calculated a tolerance for a particular thread.

Thank you for any help you can give,
GB M.E.

 

[Flareman]

I use Quattro daily and, franky, find it much easier that Excel, but, hey! MS has the pull.

Try putting your Hlookup value independently as a cell result and use the Vlookup to reference that cell value.

Hope it works for you.
There are some newer commands available like INDEX which isn't quite the same but might be OK depending on your logic sequences.

 

[Box]

Thank you,

Hadn't thought of that. I know it will work. But I'm still going to try to figure the syntax to get it all in one. It's one of those problems that I have to see solved. Being an engineer, I'm sure you understand.

Thanks again,
Gary

 

[Flareman]

Just had a look at my Excel app.

The difference between Quattro and Excel seems to be the row reference. In Quattro you can use row 0 to represent the reference row. In Excel that's row 1. Also (looking up alphas in the horizontal, I need an intermediate row underneath with a number in it to count the columns
so my formula is still
+vlookup(ref,(block A3,c10),(hlookup(alpha ref,(block A1,c10),2))

Does this work for you?

 

[Box]

I'll try it. I'll post the results.

Thanks for the help