Excel vLookup function - I think !
Thread Starter
Excel vLookup function - I think !
Can someone help me penetrate the Help on the vLookup function in Excel.
Here's what I want to do; an aviation example. I have an Excel list with a large number of records flight details, date, time, destination, etc. The destination on these records is shown as a code, eg EGLL. I want to put alongside this the name of the destination as well.
To me the easiest way to do this is to have a second worksheet in the workbook with a list of the destination codes and their names, and to link them. vLookup appears the tool to use but I cannot understand the syntax required. I guess it would be something like this :
Sheet 1 has the data and Column A contains the destination codes.
Sheet 2 has the table of codes (Col A) and names (Col B).
In Sheet 1 insert a blank Column B alongside.
B1 is something like =vLookup( go to Sheet 2, where column A = A1 in this sheet. Get column B and put it here).
Can someone translate this into Excelese for me please - or suggest a better way.
Thank you.
Here's what I want to do; an aviation example. I have an Excel list with a large number of records flight details, date, time, destination, etc. The destination on these records is shown as a code, eg EGLL. I want to put alongside this the name of the destination as well.
To me the easiest way to do this is to have a second worksheet in the workbook with a list of the destination codes and their names, and to link them. vLookup appears the tool to use but I cannot understand the syntax required. I guess it would be something like this :
Sheet 1 has the data and Column A contains the destination codes.
Sheet 2 has the table of codes (Col A) and names (Col B).
In Sheet 1 insert a blank Column B alongside.
B1 is something like =vLookup( go to Sheet 2, where column A = A1 in this sheet. Get column B and put it here).
Can someone translate this into Excelese for me please - or suggest a better way.
Thank you.
Join Date: Jan 2008
Location: London, England
Age: 56
Posts: 300
Likes: 0
Received 0 Likes
on
0 Posts
As a small example:
In a blank Excel WorkBook, go to Sheet2.
In A1 put a column header "Airport Code"
In A2 put a column header "Airport Name"
In A3 put a column header "Runway Length"
You can add as many columns as you like, but this will suffice.
Put a few codes, in column A, I just put in AAA, AAB, AAC and so on for 10 values.
Put the names and runway lengths, I just used "Airport 1", "Airport 2", etc, and 1001, 1002, 1003 for the length. That way I can see if the function is working correctly.
So had something like
A B C
1 Airport Code Airport Name Runway Length
2 AAA Airport 1 1001
3 AAB Airport 2 1002
4 AAC Airport 3 1003
5 AAD Airport 4 1004
6 AAE Airport 5 1005
7 AAF Airport 6 1006
8 AAG Airport 7 1007
9 AAH Airport 8 1008
10 AAI Airport 9 1009
11 AAJ Airport 10 1010
Now select the data cells from the first column, in this case from AAA to AAJ. Near the top left of the Excel window, there is the coordinates box, it will have "A2" in this example, or the coordinate of the top left box of the selected region. Click in here and type "AirportCodes", note no spaces!
Now select all three columns of the dataset, i.e. A2, down to C11. Give this set a name of "AirportCodeDB"
This creates a named data set for use later.
Go to Sheet1
Click any cell you like. We will use this one for the airport code drop down list. Click "Data" in the menu bar, and select "Validation...". In the pop-up window, set "Allow" to list, and set the source for the list to "=AirportCodes", again no spaces and don't forget the equals sign. Normally you would be able to drag and select the range of values for the list directly from the worksheets. However, for validation, this doesn't work when the list is in another worksheet. This is why the list data set has been given a name, because these named sets are usable across all sheets. Silly huh!
Anyway, OK the validation pop-up, and you should see a drop down arrow to the right of the field you had selected, clicking on this will reveal the list and you can simply click a value.
Right...
In the next field to the right, type "=vlookup("
Now click the cell with the dropdown list, in my case I had chosen A3.
Then type ",AirportCodeDB,2)" and hit enter.
If you click that cell now you should see something similar to this...
=VLOOKUP(A3,AirportCodeDB,2)
Where A3 will be perhaps different in your case.
In the next cell over do the same except the last digit is 3, i.e.
=VLOOKUP(A3,AirportCodeDB,3)
Now change the drop down value, and what you should find is that when you change the drop down, the corresponding Airport Name and Runway Lengths we put into Sheet2, should instantly appear in their respective cells alongside.
One thing to watch out for, is that if the dropdown box is empty, or the VLOOKUP doesn't find an entry, you may find that the VLOOKUP simply returns the last entries in the list, i.e. "Airport 10" and "1010".
Good luck!
In a blank Excel WorkBook, go to Sheet2.
In A1 put a column header "Airport Code"
In A2 put a column header "Airport Name"
In A3 put a column header "Runway Length"
You can add as many columns as you like, but this will suffice.
Put a few codes, in column A, I just put in AAA, AAB, AAC and so on for 10 values.
Put the names and runway lengths, I just used "Airport 1", "Airport 2", etc, and 1001, 1002, 1003 for the length. That way I can see if the function is working correctly.
So had something like
A B C
1 Airport Code Airport Name Runway Length
2 AAA Airport 1 1001
3 AAB Airport 2 1002
4 AAC Airport 3 1003
5 AAD Airport 4 1004
6 AAE Airport 5 1005
7 AAF Airport 6 1006
8 AAG Airport 7 1007
9 AAH Airport 8 1008
10 AAI Airport 9 1009
11 AAJ Airport 10 1010
Now select the data cells from the first column, in this case from AAA to AAJ. Near the top left of the Excel window, there is the coordinates box, it will have "A2" in this example, or the coordinate of the top left box of the selected region. Click in here and type "AirportCodes", note no spaces!
Now select all three columns of the dataset, i.e. A2, down to C11. Give this set a name of "AirportCodeDB"
This creates a named data set for use later.
Go to Sheet1
Click any cell you like. We will use this one for the airport code drop down list. Click "Data" in the menu bar, and select "Validation...". In the pop-up window, set "Allow" to list, and set the source for the list to "=AirportCodes", again no spaces and don't forget the equals sign. Normally you would be able to drag and select the range of values for the list directly from the worksheets. However, for validation, this doesn't work when the list is in another worksheet. This is why the list data set has been given a name, because these named sets are usable across all sheets. Silly huh!
Anyway, OK the validation pop-up, and you should see a drop down arrow to the right of the field you had selected, clicking on this will reveal the list and you can simply click a value.
Right...
In the next field to the right, type "=vlookup("
Now click the cell with the dropdown list, in my case I had chosen A3.
Then type ",AirportCodeDB,2)" and hit enter.
If you click that cell now you should see something similar to this...
=VLOOKUP(A3,AirportCodeDB,2)
Where A3 will be perhaps different in your case.
In the next cell over do the same except the last digit is 3, i.e.
=VLOOKUP(A3,AirportCodeDB,3)
Now change the drop down value, and what you should find is that when you change the drop down, the corresponding Airport Name and Runway Lengths we put into Sheet2, should instantly appear in their respective cells alongside.
One thing to watch out for, is that if the dropdown box is empty, or the VLOOKUP doesn't find an entry, you may find that the VLOOKUP simply returns the last entries in the list, i.e. "Airport 10" and "1010".
Good luck!
Official PPRuNe Chaplain
Join Date: Apr 2001
Location: Witnesham, Suffolk
Age: 80
Posts: 3,498
Likes: 0
Received 0 Likes
on
0 Posts
My logbook has a similar function. The ICAO code is in Column G of the "logbook" page.
The "lookup" is on a separate page in the same workbook called "ICAO Codes", which I can update as and when I need to.
Column B on that page is ICAO codes, sorted alphabetically.
Column C is airfield names.
At the moment, it allows for 196 ICAO airfields, but that's adjustable - just change "203" to the new number.
The field in the "logbook" page that displays the Airfield Name has this in it (the G7 becomes G8 G9 etc, but the lookup details stay as they are).
=LOOKUP(G7,'ICAO Codes'!$B$7:$B$203,'ICAO Codes'!$C$7:$C$203)
The only catch is that if your ICAO table has, for example, EGMC them EGMH and you fly to EGMD, it won't give you an error message - just the wrong airfield name.
The "lookup" is on a separate page in the same workbook called "ICAO Codes", which I can update as and when I need to.
Column B on that page is ICAO codes, sorted alphabetically.
Column C is airfield names.
At the moment, it allows for 196 ICAO airfields, but that's adjustable - just change "203" to the new number.
The field in the "logbook" page that displays the Airfield Name has this in it (the G7 becomes G8 G9 etc, but the lookup details stay as they are).
=LOOKUP(G7,'ICAO Codes'!$B$7:$B$203,'ICAO Codes'!$C$7:$C$203)
The only catch is that if your ICAO table has, for example, EGMC them EGMH and you fly to EGMD, it won't give you an error message - just the wrong airfield name.
Join Date: Aug 2007
Location: Lincoln
Posts: 1
Likes: 0
Received 0 Likes
on
0 Posts
Very thorough answer by MacBoero, I've used vlookup myself quite a bit but never played round with the Validate stuff, very interesting and at least I've learnt one new thing today
But to answer WHBM's question as simple as I can, your formula wants to be something like:
=vlookup(cell which contains the airport code you want to look up, the full data range of airports and their codes, the column in that range which contains the data your after, and finally unless the data range is in ascending order put a 0 in here for an exact match)
cell b1 (assuming a1 is where your first airport code is) should end up looking like =vlookup(a1,Sheet1!a1:b30,2,0)
the a1:b30 is just a guess to the size of your data range btw
But to answer WHBM's question as simple as I can, your formula wants to be something like:
=vlookup(cell which contains the airport code you want to look up, the full data range of airports and their codes, the column in that range which contains the data your after, and finally unless the data range is in ascending order put a 0 in here for an exact match)
cell b1 (assuming a1 is where your first airport code is) should end up looking like =vlookup(a1,Sheet1!a1:b30,2,0)
the a1:b30 is just a guess to the size of your data range btw
Jack's Granddad
I have found that the easiest way to use VLOOKUP and get the syntax right is as follows:
For the sake of this exercise start off by putting the data of Codes and Destinations in two columns in sheet one, say in G1 to 10 and H1 to 10. It is essential that the vertical list of destination codes in column G is in ascending alphabetical order (do this by highlighting cells G1 to H10 and using the Data Sort tool to sort column G). Check that the destination names still correspond correctly with the codes.
Now type the destination code into cell A1
a) Highlight cell B1. Click on Insert---Function---Lookup & Reference---VLOOKUP
b) Fill in the boxes as follows: in the ‘Lookup value’ box enter A1 (no $ sign); in the ‘Table Array’ box type G$1:H$10 (the $ signs in the cell names ensure that just those cells are referenced for every lookup performed in column B); in the ‘Col Index No’ box type 2. Leave the ‘Range Lookup’ box blank.
c) Test by inserting a code into cell A1 and ensure that the correct destination name comes up in B1.
d) Copy the contents of B1 down column B as many times as you wish. Every time you enter a code in column Ax the destination should appear in Bx.
e) Highlight the table G1:H1. You can then cut and past it to either somewhere more inconspicuous in sheet one, or if you wish, in sheet two. The VLOOKUP function you have entered in B1 will adjust appropriately.
Think I have understood your query correctly and hope this helps. Send me a PM if I can help further.
For the sake of this exercise start off by putting the data of Codes and Destinations in two columns in sheet one, say in G1 to 10 and H1 to 10. It is essential that the vertical list of destination codes in column G is in ascending alphabetical order (do this by highlighting cells G1 to H10 and using the Data Sort tool to sort column G). Check that the destination names still correspond correctly with the codes.
Now type the destination code into cell A1
a) Highlight cell B1. Click on Insert---Function---Lookup & Reference---VLOOKUP
b) Fill in the boxes as follows: in the ‘Lookup value’ box enter A1 (no $ sign); in the ‘Table Array’ box type G$1:H$10 (the $ signs in the cell names ensure that just those cells are referenced for every lookup performed in column B); in the ‘Col Index No’ box type 2. Leave the ‘Range Lookup’ box blank.
c) Test by inserting a code into cell A1 and ensure that the correct destination name comes up in B1.
d) Copy the contents of B1 down column B as many times as you wish. Every time you enter a code in column Ax the destination should appear in Bx.
e) Highlight the table G1:H1. You can then cut and past it to either somewhere more inconspicuous in sheet one, or if you wish, in sheet two. The VLOOKUP function you have entered in B1 will adjust appropriately.
Think I have understood your query correctly and hope this helps. Send me a PM if I can help further.
Join Date: Oct 2000
Location: Sunny Sussex
Posts: 778
Likes: 0
Received 0 Likes
on
0 Posts
One thing to remember about vlookup is it returns a result counting in from the first column of the array you are looking up, so you don't want to have a gap in there for neatness.
Great & useful function vlookup, I use it daily & a rare thing, something handy from microsoft.
Great & useful function vlookup, I use it daily & a rare thing, something handy from microsoft.
Join Date: Feb 2003
Location: Scotland
Posts: 144
Likes: 0
Received 0 Likes
on
0 Posts
You can sacrifice a little speed for an exact match. VLOOKUP's fourth parameter defines whether the list is sorted. If the parameter is TRUE (the default) it will return a value every time, if your desired entry isn't in the list you will get a return which is the closest. If you set the parameter to FALSE you don't need to sort the list but you will get an error message if it cannot find your value.
Join Date: Jul 2017
Location: Indiana
Posts: 1
Likes: 0
Received 0 Likes
on
0 Posts
Applying a Vlookup is really simple:
The Syntax of Vlookup is : =VLOOKUP( lookup_value, table_array, column_index, range_lookup )
where:
1. ‘lookup_value’ specifies the value to be searched inside the ‘table_array’.
2. ‘table_array’ is the range with two or more columns.
3. ‘column_index’ is the relative index of the column whose value needs to be returned by the VLOOKUP function.
4. ‘range_lookup’ is a Boolean value that specifies whether you want VLOOKUP to find an exact match or an approximate match.
http://spreadsheeto.com/wp-content/u.../2015/10/3.png
I would suggest you to have a look at this article for further details on the topic.
The Syntax of Vlookup is : =VLOOKUP( lookup_value, table_array, column_index, range_lookup )
where:
1. ‘lookup_value’ specifies the value to be searched inside the ‘table_array’.
2. ‘table_array’ is the range with two or more columns.
3. ‘column_index’ is the relative index of the column whose value needs to be returned by the VLOOKUP function.
4. ‘range_lookup’ is a Boolean value that specifies whether you want VLOOKUP to find an exact match or an approximate match.
http://spreadsheeto.com/wp-content/u.../2015/10/3.png
I would suggest you to have a look at this article for further details on the topic.
