Understanding the VLOOKUP Function

Microsoft should remove the requirement that the list should be in sorted order, because users who update table data, say, with new part numbers, typically add the data to the end of the table, but do not re-sort the data. This makes VLOOKUP unreliable. The app should sort the data internally before doing the lookup, and not depend on user proficiency.

Probably good to publish some more information about the new XLOOKUP function ?

how to vlookup email id's sql database .xml file, and how to remove duplicates (both duplicates)...

A toughie(?). I have a spreadsheet with COL A with Award1-Award100 (A2:A101). COL B has students from a dropdown list categorized by grade level in elementary school for each award, and by grade and homeroom in middle school (contiguous except for fill-in breaks denoting grade level change ex. Sixth grade Jones, then after 22 students Sixth grade Kyle, etc.). Column C is the award dropdown list; Col D is grade level, manual entry; Col E is dropdown for Stage Award or Classroom Award; COL F is Teacher giving the Award - manual entry; all these COLUMNS are good to go and work fine. Here's the challenge: COL G needs to populate with a specific Homeroom teacher when a student's name is chosen from COL B. I have the source data in a hidden sheet (COL K for any who answer this). Example: If someone in the Smith homeroom is chosen to receive an award, then COL G will show Smith as the homeroom teacher. Once this spreadsheet is finished, the teachers send it to me and I concatenate the sheets or send them singly to the school Principal. He wants the homeroom teacher to populate automatically and the teachers are expecting the worksheet May 4th to start working on it.

Call the first worksheet Awards, and the hidden data sheet Source. Thanks. I love working in excel, but I do a lot more than spreadsheets in my job as Teacher and Campus Technologist.

Got an error while posting comment 2 of 2 so I had to further break down my post. This is a continuation to my Comment 2 of 2.
.
If you type 1000 in Cell C1 the output is
India, Wonderful,
If you type 2000 in Cell C1 the output is
Japan, Rising Sun,
If you type 3000 in Cell C1 the output is
America, Hollywood.

The macro is not written by me. I got it at some discussion forum and I credit his due contribution. But I exclusively used this in my assignments without encountering any major problems so far. Thanks.

Comment 2 of 2
.
---------------------------------------
On the Excel Sheet1,
Let in Column A,

Cell A1 = 1000
Cell A2 = 2000
Cell A3 = 3000
Cell A4 = 3000
Cell A5 = 1000
Cell A6 = 2000

Let in Column B,

Cell B1 = "India"
Cell B2 = "Japan"
Cell B3 = "America"
Cell B4 = "Hollywood"
Cell B5 = "Wonderful"
Cell B6 = "Rising Sun"

Let Cell C1 be the reference cell

Key in the formula below in any other cell (say D1)

"=FindSeries(A1:A6,C1)"

Comment 1 of 2

This is a lookalike of 'vlookup' function.
It displays contents of all cells (seperated by comma) in a column corresponding to similar values of a previous column. Try this example.

Open a new Excel workbook.

In a module in View Code type the code below:
----------------------------------------
Public Function FindSeries(TRange As Range, MatchWith As String)

For Each cell In TRange
If cell.Value = MatchWith Then
x = x & cell.Offset(0, 1).Value & ", " & Chr(10)
End If
Next cell
FindSeries = Left(x, (Len(x) - 2))
End Function

'Note: "Chr(10)" above inserts a line feed in the output

Hi Nukecity,

I meant the curly braces around the {1,2}, not around the whole formula. I did not enter the outer curly braces around the whole formula and it worked.

Hi Nukecity,

I did not enter it as an array formula and it worked just fine. I just entered it with the curly braces as a regular formula and it worked for me.

Norm's tip combining VLOOKUP and CHOOSE deserves one comment: the formula needs to be entered as an array formula - e.g. ={VLOOKUP(B24,CHOOSE({1,2},$E$25:$E$30,$C$25:$C$30),2,FALSE)}.

I've been using VLOOKUP for the last 18 months, to merge information from two different monthly reports on a range of products, since our accounting program doesn't give both together.

@Debbie,
I hope the linked picture helps you.
http://jpg.co.il/download/52d11c34815a2.png
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL

Debbie,
Back to your original question about using VLOOKUP, and can you "flag" that the data table actually has duplicates. An esy way to simply know IF there are duplicates (but not identify their location for you) is to use the COUNTIF function.
Say your vin #'s are in A2:A2000, and the vin # you are searching for was entered in F2.
=COUNTIF(A2:A2000,F2) will tell you how many matches are found.
=IF(COUNTIF(A2:A2000,F2)>1,"duplicates","unique") will tell you in words.
to make it really jump out at you, you can use conditional formatting on the cell containing this formula - maybe tell it to use red font and bold text if it contains the word "duplicates".

Debbie,
to find duplicates:
1) select range (cells containing vin #'s)
2) on Home tab, select Conditional Formatting > New Rule > "Format only unique or duplicate values" 5th option.
3) make sure drop-down box shows "duplicate" (not "unique")
4) then, in this same dialog box, select Format > Fill > select your color (let's say orange)
5) click OK
6) click OK
Now, all your duplicates should be colored orange.
Using autofilter, select "Filter by Color" > choose orange.
Now your list will be filtered to show only those vin #'s with duplicate entries in the range you selected in step 1.
To go back to the full list, use autofilter to "Clear Filter from [your-column-header]"

Some more info about my previous question.
I have a list of vehicle id #'s from the factory. They sort their lists by the last 6 of the vin#. They also list separate sections where the same vin may be in multiple sections. I also find that the same last 6 digits may belong to two different vehicles.
I start with this big mass list of vins and would like to know if the same last 6 digits in the column appear more than once in the long list. How do I do this? I can't do a find for each of the vin's in the report. It would be faster to sort it and just look down the list for dups and then put it back into original sort format.

I too use Index/Match instead, you just have to make sure the start rows of both arrays are the same.

=index(i5:i27,match(c28,h5:28,0))

Not only do I not have to count columns, but I can get data that is located in a column to the left of the column I am searching in. You can also do the match in a separate column then use that to do find multiple pieces of data from the same line. For instance I could do a match on a customer number. Then reference that match to get the customer name and address. It is also much faster to do a single match then to do multiple lookups.

After switching to Index/match I haven't seen a reason to use Vlookup, am I missing something?

Used H/Vlookup for many years from Excel 5 onwards. However the lookup function is a memory hog and can be very cumbersome when used in very large data base environments. Combining Match and Index functions is far more exacting fro results and flexible in manipulation and does not make unreasonable demands on resources. Sorting is not necessary with M&I. Giving your database a range name is also recommended and the data itself should always be contiguous.

Vlookup and SumIf(s) without a doubt some of the most useful functions in excel.

Although I almost always use vlookup with false.

As to Norm's comment, I found the following on myOnlineTraininghub:

Now, on its own, like the example above, CHOOSE is not much use but when you use it in a VLOOKUP it enables us to trick Excel into returning the value to the left of our lookup column.

Our formula to look up date 29th January 2011 in column K and return the value in column E (column number 2) is:

=VLOOKUP(DATE(2011,1,29),CHOOSE({1,2},$K$2:$K$207,$E$2:$E$207),2,0)Translated:

=VLOOKUP(find 29/01/2011 in column K and return the value in column E)

I've always preferred Index-Matching, but only because I'm too lazy to count columns:

=INDEX(A:A,MATCH(B:B,C:C,0)

where Col A contains the value you're looking for, and Col B & C have the values you're comparing to one another. The zero makes it look for exact matches.

Norm- I'm intrigued; how is the {1,2} arguement interpreted by the CHOOSE function? Will it find a match in column E and return the result from column D? Was there supposed to be a link to an example? Thanks.

I use it with job numbers. In my spreads I enter the job number and I get a return of the Customer and Client. It keeps the information the exact same for all entries for that specific job number. I use VLOOKUP all the time for similar needs.

Suzy,

Try using conditional formatting to find duplicates. It works great.

Debbie:

You may want to use conditional formatting to highlight those items in your spreadsheet that match that six digit code rather that using VLOOKUP.

Oops, sorry, Debbie.

Debra,
You might want to read the help for the COUNTIF function. This may be helpful to you, as it can tell you how many cells in a range fit your criteria.

I use VLOOKUP a lot to match a 6 digit string in an ID number. Occasionally I have the same 6 digit string listed for 2 different items. Is there any way to have it flag this or notify me? I find these by lots of research and it is time consuming in a huge table.

I learned this a long time ago. I have since shown a lot of people who still to this day thank me for such a time saving tool. In the past they manually did what VLOOKUP does automatically.

My favorite VLOOKUP formula is when you combine VLOOKUP with the CHOOSE function.
Which allows you see search to the left

=VLOOKUP(B24,CHOOSE({1,2},$E$25:$E$30,$C$25:$C$30),2,FALSE)

SEE EXAMPLE

Found this useful -

http://officeimg.vo.msecnd.net/en-gb/files/818/530/AF101984660.pdf

Vlookup() and Hlookup() are basic database get functions. They look for a specific record and return as result.

There are database functions available to find totals. Look at the D-functions like DGet(), DSum(), DCount(), etc.

To find duplicates using conditional formating.

how can i use the vlookup to find total ?

How can I use the v lookup function to find duplicates? That is, I want to find all the occurences in Col B of each cell from Col A...
Thanks!

The last sentence about keeping the table sorted in ascending sequence does not apply if you use the "FALSE" optional parameter, meaning, I want exact matches. In my case, I needed to keep the table sorted in a different sequence.

The easiest and best explanation of VLookup