why is it that when you look up a value using vlookup the value always has got to be in the first row ? or can i fix this ?
why is it that when you look up a value using vlookup the value always has got to be in the first row ? or can i fix this ?
It never has to be in the first row!, or in the first column for that matter.
It depends how you set up your database. If you want to look up pricing,
for example, then it is obvious that your list will contain the criteria -
part number, part description - in the leftmost column of the list, which
could start in Column AA for that matter. What you cannot do, is have your
part number in say column G, and your pricing in colmn B, it will not work.
Similarly, it is not logical to have an address list starting with telephone
numbers in Col A, and then area codes, City, Suburb, Street Address, Street
number in Cols B - F, and the resident's name in col G. After all, you will
always work with the resident, so put it first. That is the known criteria,
the rest is only required on an as and when required basis, therefre they are
to the right of the name column.
"Zygan" wrote:
>
> why is it that when you look up a value using vlookup the value always
> has got to be in the first row ? or can i fix this ?
>
>
> --
> Zygan
> ------------------------------------------------------------------------
> Zygan's Profile: http://www.excelforum.com/member.php...o&userid=34423
> View this thread: http://www.excelforum.com/showthread...hreadid=548238
>
>
Hi Zygan,
You can use a combination of INDEX() and MATCH()
Have a look at this link
http://www.mrexcel.com/tip021.shtml
Regards,
Bondi
Two things to watch out for:
1) is the table sorted or not, if sorted use True; otherwise, False
2) the table itself should be a named table or be addressed with
absolute references.
Vlookup Workdsheet Function
http://www.mvps.org/dmcritchie/excel/vlookup.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Zygan" <[email protected]> wrote in message
news:[email protected]...
>
> why is it that when you look up a value using vlookup the value always
> has got to be in the first row ? or can i fix this ?
>
>
> --
> Zygan
> ------------------------------------------------------------------------
> Zygan's Profile: http://www.excelforum.com/member.php...o&userid=34423
> View this thread: http://www.excelforum.com/showthread...hreadid=548238
>
3) don't mix cells that are all digits with text cells.
The reason i ask is because my table needs to look up in two areas for marketing reasons e.g name is fine, but i would like people with the postcode of #### to show up and then i can achieve a marketing campaign on these results like you said kassie the postcode is on a "if required basis" i had a look at the tip Bondi gave me and it worked well thanks for the advice all
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks