| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 63310 | 2005-11-05 01:23:00 | Got some problem with vlookup/hlookup | jun47 (9206) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 402070 | 2005-11-05 01:23:00 | Hi! anybody there who can help me? or having the same problem as I? I use vlookup and hlookup in my formulas and they getting a wrong value in different fields. Like having column 1 and 2 having the value which only 1 should have. |
jun47 (9206) | ||
| 402071 | 2005-11-05 01:35:00 | Hello and welcome to PressF1. Without seeing your formula (you may want to post it) and an example, it is hard to visualise how it is giving the wrong answer. It is possible the numbers being looked up are not in order - if this is the case then you need to add a ,False to the end of your formula (before the final bracket). Some information on the lookup function is available here. (office.microsoft.com) If this doesn't help, post a reply with some more information. Andrew |
andrew93 (249) | ||
| 402072 | 2005-11-05 06:42:00 | thanks. i'll be off work for the weekend so i will have to try it early next week. i'll update you if it works. if not i'll try to put a sample file here. | jun47 (9206) | ||
| 402073 | 2005-11-05 09:36:00 | No worries. Thanks to Dreamboat there is also a good description of the vlookup function here (www.officearticles.com) that you might want to read. HTH, Andrew :) |
andrew93 (249) | ||
| 402074 | 2005-11-05 11:59:00 | Hi, a common mistake people make with these formulas is they enter the incorrect data range to look at or the incorrect column to return. As an example for VLOOKUP, the 2nd argument is the data range to look at where the first cell in this range must be in the column where the value your looking for is located and the last cell in the range must include up to the column you want to return. The 3rd argument is a number representing the column you are looking up, but must account for the starting column in the 2nd argument. eg: Your data is A1:L100. Your looking up the text "Jun47" in column D and want to return the value of column H in the found row. =VLOOKUP("Jun47",$D$1:$H$100,5,False). So the value we looking for is in column D so we start at D1 and the last column we look at is column H. I could have used D1:L100 as well because it's the 3rd argument that determines which column to look at being the 5th column across from column D (also including D -ie D=1, E=2 etc). Notice that the data range D1:H100 has those dollar signs which indicate an absolute range. This is because if you drag this formula down then it will still look at the correct data range. If not, you may find VLOOKUP will "miss" some lookups because the starting and ending data range has moved automatically as part of Excels functionality to amend its reference in relation to where you started. The last argument False means we want an exact match not a partial one. The same principles apply to HLOOKUP. If your wanting to lookup data in a column left of the lookup range (eg you want to look up column D but return the value for column B) then VLOOKUP will not work. Instead you have to use a combination of 2 formulas - INDEX and MATCH. office.microsoft.com |
Parry (5696) | ||
| 1 | |||||