How to Vlookup & Hlookup??? 2

[RookieDBO]

I'm having trouble understanding vlookup and Hlookup. Would someone be kind enough to explain how it works? Also, can someone send me an example at vigorous1@yahoo.com.

Thanks,
RookieDBO

 

[patriciaanne]

Excel allows you to look up the contents of various cells within a table using Vlookup and Hlookup. Vlookup searches down the column of the table whereas Hlookup searches a column.

Be aware of the following:
In Excel the data in lookup table should be sorted otherwise you will not necessarily receive the correct answer. Even if the data is sorted you may not receive the correct answer as Excel tries to give you the next closest value if there is not an exact match so it is recommended that you use the word False in the formula.
LOOKUP Syntax: =VLOOKUP (lookupvalue, table_ array, col_index_number, range lookup, False)
Lookup_Value The cell you want Excel to lookup or match

Table Array The location (range of cells) of your lookup table (should be an absolute cell reference)

Col. Index Number The column number in the lookup range that has the data you are seeking

False If there is not an exact match, a value is not returned; Use true or blank if you don't need an exact match

I'll email you a simple example that I use in my class.
patricia

 

[LSTAN]

Hi

Here is an eample;

Sub VLookUpFunction()
Dim MyData As Range
Dim SearchCriteria As Variant

' Initialize an object variable for the range of data

Set MyData = Worksheets("Sheet1&quot.Range("A1:B10&quot

' Initialize a variable for the VLOOKUP criteria value

SearchCriteria = "Apple"

' Execute the VLOOKUP function and return the result in a message box

MsgBox Application.VLookup(SearchCriteria, MyData, 2, False)

End Sub


This code will search the column 1 for the SearchCriteria if found will return the corresponding value at column 2.


Helpful Notes - VLOOKUP Function Arguments :
· lookup_value - the value to be found in the first column of the array

· table_array - the table of information in which data is looked up

· col_index_num - the column number in table_array from which the matching value must be returned. A 2 is used in our example so the VLOOKUP function returns a value from the 2nd column in the table array.

. range_lookup - A False is used in our example so the VLOOKUP function will only return an exact match.

rgds
LSTAN

 

[RookieDBO]

Thanks guys.

RookieDBO

 

[SkipVought]

Hi,

Let me add a little icing by using Named Ranges.

Suppose that you have a Table like this...

Name    Age     Rank
skip    55      3
jerry   44      4
mary    33      5

and you name the data region, "DBase"
and you create names using the headings in the Top - Insert/Name/Create - Top row

Then, instead of your VLOOKUP looking like this to find mary's age...

=VLOOKUP("mary",A2:C4,2)

it would look like this...

=VLOOKUP("mary",DBase,COLUMN(Age))

And so much more can be clarified by using named ranges. Skip,
metzgsk@voughtaircraft.com

 

[LECXE]

HI PATRICIAANE
If you want excel to reloop the formula that is without having to change your sort order and find your matches, then you must enter $

=vlookup(A1,$B$1:$C$2,2,false)

 

[patriciaanne]

You are correct that you need the table to be an absolute cell reference but I don't think that impacts the sort order. Even with the $ you will receive the wrong answer if your data is not sorted and you did not use the FALSE. I think the FALSE is the key. The Index Match function is gaining a lot of popularity simply because you don't have to sort you data.

 

[Rhonda1223]

This is an interesting thread as I have a lot of users interested in lookup functions....but I have a couple questions:

1-When is a lookup beneficial? I agree with patricia that you have to sort that data before anything else. Therefore, if it's sorted, wouldn't it be easier to scroll down to what you're looking for and then find the desired info? Sounds easier to me than writing a formula.

2-What is the Index Match function all about?

 

[xlbo]

Ok - 1st:
VLOOKUP and HLOOKUP do NOT need the data to be sorted IF you use FALSE as the 4th argument.

2nd: If you have a list of accounts in 1 workbook with various figures...debt / orders /credit etc etc next to them and in another workbook, you have a subset of this list with just account numbers is it quicker to
a: Manually search through the initial set of accounts 100 times (1 for each account on the subset sheet) and manually enter the appropriate data next to them or
b: type a formula next to the 1st account number and then fill down to the bottom to get the appropriate information ???

3rd: INDEX MATCH formulae are very useful in certain situations. These 2 functions can be combined to perform the same kind of result as a lookup formula. There are some differences however......
INDEX / MATCH formulae can lookup right to left as well as left to right (vlookup is left to right only)
INDEX / MATCH formulae calculate quicker as excel creates a memory index much like you can create on a database for quicker queries - NB, this is only really noticeable on large datasets with many lookups
INDEX / MATCH are 2 formulae, used in combination and therefore, the formula will be longer - this is more of a problem if the lookup is part of a longer formula
VLOOKUP formulae are generally easier to manipulate where you want to lookup various different bits of information from a datasheet as it is easier to assign the number of columns across the lookup needs to return

phew ;-) [/rant] Rgds
~Geoff~

 

[patriciaanne]

Rhonda-
Let me give you an example. On one excel sheet, you may have a listing of all your products and their accompanying October 2002 month sales. Suppose you have another file showing all your products and their October 2001 sales and you want to compare them to see how sales are doing by product. Now consider, you may have hundreds of products and you may have some new ones in 2002 that weren't available in 2001 and some products in 2001 that were discontinued in 2002. If you manually looked all that up you would be there a long time. Vlookup would look at the product name on the first sheet and then go look and match it on the second sheet and return the respective sales information. How my example helps.

Index and match are two separate functions that you can marry together. In my mind it is a little more complicated but you don't have to sort your data and you can also tell Excel that if it can't find an exact match to select the next lowest value or the next higher value so it gives you a little more control. If you are interested I can send you a spreadsheet showing how it works.
Patricia

 

[Rhonda1223]

Thank you all for your input.

Patricia -
Could you please send me a spreadsheet so I can get an example? Thank you very much! trk1616@aol.com

 

[patriciaanne]

Great explanation Geoff. One of my friends and I have been debating index match versus vlookup. I didn't realize the strengths of index match .. Thanks...Patricia

Rhonda- I will email you a file shortly.

 

[Dreamboat]

Sample VLOOKUP download file:

http://www.theofficeexperts.com/downloads.htm#ExcelDownloads

Hope it helps someone. Anne Troy

http://www.TheOfficeExperts.com