Mayank Kaushik
New Member
Hi Chandoo,
How to use Vlookup & the use of Vlookup in excel.
Thanks,
Mayank
How to use Vlookup & the use of Vlookup in excel.
Thanks,
Mayank
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
How to use Vlookup in Excel.
- Thread starter Mayank Kaushik
- Start date
GeorgeF211
New Member
What specifically are you trying to use it for?
Mayank Kaushik
New Member
How to use reverse lookup
anand_babu
New Member
Try using Index + Match functions
VLOOKUP(Lookup Value, Table Array, Column Num, True/False)
Lookup Value: the value in which you are searching for
-- this can be a 'hard value' that you simply type in or it could be a cell reference if the value changes or already exists in a cell.
Table Array: the cell range in which the function is to start looking for the specified 'Lookup Value'
-- KEY: the first column in the cell range MUST contain the value that you are looking for. Ex: if you are looking for a first name, the Table Array cell range cannot start with a column containing phone numbers (it can, but the formula will never work)
-- KEY: This function will only look in the first column specified in the Table Array (which is why the above KEY point is imparative.
Column Index Number: which column in the Table Array contains the value in which you are looking for. if you Table array is A1:E10 and you are wanting the value from Col.D, you would put 4 (A=1,B=2,C=3, etc.) HOWEVER: if you specify the Table Array as C1:F10; C=1, D=2, E=3, etc...
True/False ('Range Lookup'): True will search for an approximate match to the Lookup Value and FALSE will only search for an exact match to the Lookup Value.
-- KEY: False is most common. If an exact match can not be found, the function will return the #N/A error. Either the value doesnt exist, the table array is not correct, or you could have some 'hidden' characters (like spaces) that you can see.
SAMPLES:
| Col. A | Col. B | Col. C | Col. D |
1| Name | ID | Dept. | Phone |
2| Bob | 1234 | HR | 123-1234 |
3| Luke | 0987 | Security | 123-5467 |
=VLOOKUP(E1,A1:D3,2,False)
In E1 we have typed "Luke". The formula will look at the value of E1, see "LUKE" and then go to the first column in the Table Array (A1:D3) and look down vertically for "LUKE". once it finds it, it will go to the Col. # (we had 2) and return the ID# for the Record containing "LUKE" in the first column. If we were to type "BOB" in E1, it would then change to return Bob's ID#.
If we changed the formula to have a different Col.#, say 4, it would return the Phone Number for that person.
If you type in Jason in E1, it would return #N/A because it will not find an exact match in the Table Array.
Check the following link:
http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx
As Anand said, the INDEX + MATCH combination could be more helpful depending on the layout of your data.
INDEX(ARRAY,ROW#,COL#) to use these together you would place the MATCH Funciton in place of the ROW# argument: INDEX(ARRAY,MATCH(LOOKUP VALUE,LOOKUP ARRAY,MATCH TYPE),COL#)
Lookup Value: the value in which you are searching for
-- this can be a 'hard value' that you simply type in or it could be a cell reference if the value changes or already exists in a cell.
Table Array: the cell range in which the function is to start looking for the specified 'Lookup Value'
-- KEY: the first column in the cell range MUST contain the value that you are looking for. Ex: if you are looking for a first name, the Table Array cell range cannot start with a column containing phone numbers (it can, but the formula will never work)
-- KEY: This function will only look in the first column specified in the Table Array (which is why the above KEY point is imparative.
Column Index Number: which column in the Table Array contains the value in which you are looking for. if you Table array is A1:E10 and you are wanting the value from Col.D, you would put 4 (A=1,B=2,C=3, etc.) HOWEVER: if you specify the Table Array as C1:F10; C=1, D=2, E=3, etc...
True/False ('Range Lookup'): True will search for an approximate match to the Lookup Value and FALSE will only search for an exact match to the Lookup Value.
-- KEY: False is most common. If an exact match can not be found, the function will return the #N/A error. Either the value doesnt exist, the table array is not correct, or you could have some 'hidden' characters (like spaces) that you can see.
SAMPLES:
| Col. A | Col. B | Col. C | Col. D |
1| Name | ID | Dept. | Phone |
2| Bob | 1234 | HR | 123-1234 |
3| Luke | 0987 | Security | 123-5467 |
=VLOOKUP(E1,A1:D3,2,False)
In E1 we have typed "Luke". The formula will look at the value of E1, see "LUKE" and then go to the first column in the Table Array (A1:D3) and look down vertically for "LUKE". once it finds it, it will go to the Col. # (we had 2) and return the ID# for the Record containing "LUKE" in the first column. If we were to type "BOB" in E1, it would then change to return Bob's ID#.
If we changed the formula to have a different Col.#, say 4, it would return the Phone Number for that person.
If you type in Jason in E1, it would return #N/A because it will not find an exact match in the Table Array.
Check the following link:
http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx
As Anand said, the INDEX + MATCH combination could be more helpful depending on the layout of your data.
INDEX(ARRAY,ROW#,COL#) to use these together you would place the MATCH Funciton in place of the ROW# argument: INDEX(ARRAY,MATCH(LOOKUP VALUE,LOOKUP ARRAY,MATCH TYPE),COL#)