Vlookup

  • Thread starter Thread starter jacki
  • Start date Start date
J

jacki

What is the best way to be able to enter a number into a cell and it returns
the name that corresponds with that number in the cell to the right of it.
The numbers and names to view from would be on a master/seperate worksheet.

Thanks so much for your help.
 
Index/match gives you the versatility to return any col to the left or right
of the match col

Eg in Sheet2,
You could have something like this in say, C2:
=INDEX(Sheet1!A:A,MATCH($B2,Sheet1!$B:$B,0))
to return Sheet1's col A based on exact matching B2 within Sheet1's col B

The col to return is specified in this part:
INDEX(Sheet1!A:A

Inspiring? hit the YES below
 
Thanks so much for the quick reply but I guess I'm just dense.

I need to type a number in sheet 1, a2.. It will go to sheet 2 and match
that number with all the numbers in column a and copy the name next to that
number back to sheet 1,b2. I hope this makes sense as it's been awhile since
I have used Excel.

Thanks again for your time and help.
 
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
 
I need to type a number in sheet 1, a2.. It will go to sheet 2 and match
that number with all the numbers in column a and copy the name next to that
number back to sheet 1,b2
Click to expand...

In Sheet1,
Put this in B2: =INDEX(Sheet2!B:B,MATCH($A2,Sheet2!$A:$A,0))
Copy down
 
Max,

found your answer below and tried to adjust it to a question I raised today,
"colating data from a table for a bespoke report". I used it to find the
first record but have problems with it finding futher records can you help
please.

(see below for example) Basically In cell a1 I have a drop down that allows
you to select a name so that all the differant Proj Code (from a 1020 row
list) against that name are shown on seperate rows in preformated report.
Once I have that data by means of a vlookup I have a formula that will show
the rest of the data.

Proj Code Name

PC01 colin
PC01 fred jones
PC12 colin
PC13 fred jones
PC14 fred jones
PC19 colin

if you can help could you post a report against that question please.

many thanks in advance.

UKMAN
 
Back
Top