v and h lookups

[Eliza]

What is the purpose of using both a v and an h lookup?
For example, '=VLOOKUP("average",Current_Portfolio,HLOOKUP("Current Ask",Current_Portfolio,2,FALSE),FALSE)

Wouldn't it just be simpler to use: '=VLOOKUP("average",'Preauth List - Recreated'!A:P,16,0)

Thanks for your help!

Eliza

 

[Sigh]

From Excel help: "Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find".

 

[Eliza]

So why would you need to use both in a formula then instead of just specifying which row to look in within the vlookup (like in the example I gave above)?

Thanks for your help!

 

[Weaver]

Looks like the hlookup is required to get a column number from some other dataset to apply to the vlookup function. Sure, you could maybe rebuild that particular table elsewhere, but it's hard to tell what other values might be affecting the outcome.

I'm guessing you're trying to amend/pick apart someone else's work.

 

[Eliza]

yes - I have a rather complicated excel file I'm trying to decode and work with. The original author keeps using a combination of v and h lookups and I don't think its needed. I don't necessarily need to change it, but since I don't understand lookups very well, I'm concerned that I might be creating future lookups incorrectly if I don't use his format, but I'm not sure its the best format anyway...

when I search online most information seems to be about vlookups. are hlookups used less frequently in general?

thanks again for the help.

 

[Jonmo1]

Given your two example formulas...

=VLOOKUP("average",Current_Portfolio,HLOOKUP("Current Ask",Current_Portfolio,2,FALSE),FALSE)

=VLOOKUP("average",'Preauth List - Recreated'!A:P,16,0)

The Hlookup in the original, is being used to determine which column # to return in the vlookup (the 16 in your 2nd formula)

That actually is a somewhat common use...

 

[dave3009]

Hi Eliza

It might be useful for you to post a screenshot of the data and the result you get.

It seems to me that you might be able to apply INDEX to the scenario which is essentially a two-way lookup

KR


Dave

 

[Weaver]

We use VLOOKUP a lot (and I've spent a great deal of time educating people on its use!) since we use excel files as databases, where you have your fields as columns and your records as rows, pretty much like an Access table. I imagine this to be a reasonably common way to store data in excel and so I'd also imagine vlookup to be much more common than hlookup.

Sounds like you have your work cut out for you!

 

[bk]

Eliza, I'm trying to guess at what you're trying to do. And if I've guessed correctly, you should try the VLOOKUP with the MATCH function as the 3rd parameter (and not the HLookup -I cant' think of a good way to use these together, but I'm wrong every day, too).

 

[Joe_Canadian]

If the thing works, and you're just looking for a why is this person doing thigns this way, not a why doesn't this work kind of thing... The usual answer is that it is easier to understand it using a vlookup and an hlookup.

That is I can tell right away from the first formula you are looking up the average of the current ask price in the current portfolio. In the second formula, while it is shorter to write, and will likely run more quickly, unless it is very clear why are you are calculating the number there is no way to tell what it is doing just by looking at it.

The second reason is that you can fairly easily add more columns and rows to the table, and only have to change the named ranges. In your second version you would have to find everywhere you have used a column number and change it, in addition to changing the named ranges.