Vlookup

  • Thread starter Thread starter matt_the_brum
  • Start date Start date
M

matt_the_brum

I understand that this

=VLOOKUP(1,A2:C10,2)

Looks for number 1 in the first column of a table and shows the data i
the 2nd column of that row.
I thought this

=VLOOKUP(E9,Enquiries!1:65536,Enquiries!C2:C1000)

Would look to see what I typed in E9, try and find that in workshee
'Enquiries', then show what data is in Column C of that row?
Its not working.
Basically I want to type in a refernce number thats on anothe
worksheet and have the relative data pop up in the cell next to it.
Thanks for any help
 
Hi Matt

You can omit the column letters from your table and just use row numbers as
per your formula, provided the item to be looked up appears within the first
column, A.
However, the offset has to be a single value, not a rnage of values as you
have used.
You could use
=VLOOKUP(E9,Enquiries!1:65536,Enquiries!C2)
and as you copied down the C2 would chage to 3,4,5 etc if that is what you
want.

If the data is not sorted, you would also need to use th optional 4th
argumenof VLOOKUP, namely FALSE or 0.
=VLOOKUP(E9,Enquiries!1:65536,Enquiries!C2,0)
 
On re-reading this, the offset would of course alter if the values held in
C2, C3 etc. altered.
If waht you are after, is the corresponding value in column C to the value
found in Column A, then you just need to use the value 3 in the formula
=VLOOKUP(E9,Enquiries!1:65536,3,0)

or if cell C2 is is holding the value of 3, which you want to use for all
values in the column, then just anchor it with the $$
VLOOKUP(E9,Enquiries!1:65536,Enquiries!$C$2,0)
then, if you changed the value in C2 from 3 to 4 for example, you would pick
up values from column D of your data.

--
Regards
Roger Govier
Roger Govier said:
Hi Matt

You can omit the column letters from your table and just use row numbers as
per your formula, provided the item to be looked up appears within the first
column, A.
However, the offset has to be a single value, not a rnage of values as you
have used.
You could use
=VLOOKUP(E9,Enquiries!1:65536,Enquiries!C2)
and as you copied down the C2 would chage to 3,4,5 etc if that is what you
want.

If the data is not sorted, you would also need to use th optional 4th
argumenof VLOOKUP, namely FALSE or 0.
=VLOOKUP(E9,Enquiries!1:65536,Enquiries!C2,0)
Click to expand...
 
Thanks for the help Roger.
=VLOOKUP(E9,Enquiries!1:65536,3,0)
is working fine but how can I copy the formula down keeping the
Enquries!1:65536
constant and not moving down to
Enquiries!2:65537
etc
 
Back
Top