MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Vlookup

• Follow

```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.
```
 0

```Look in the help index for VLOOKUP

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"jacki" <jacki@discussions.microsoft.com> wrote in message
news:1675AFF2-5950-4D5A-B1E0-C66FA0090E64@microsoft.com...
> 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.

```
 0

```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
--
Max
Singapore
---
"jacki" wrote:
> 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.
```
 0

```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.

"Max" wrote:

> 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
> --
> Max
> Singapore
> ---
> "jacki" wrote:
> > 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.
```
 0

```      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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"jacki" <jacki@discussions.microsoft.com> wrote in message
news:582DC21E-9A5E-4992-8B09-FE6B0CFCFE9E@microsoft.com...
> 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.
>
> "Max" wrote:
>
>> 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
>> --
>> Max
>> Singapore
>> ---
>> "jacki" wrote:
>> > 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.

```
 0

```> 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

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

```Max,

"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

(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.

UKMAN

"Max" wrote:

> > 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
>
> In Sheet1,
> Put this in B2: =INDEX(Sheet2!B:B,MATCH(\$A2,Sheet2!\$A:\$A,0))
> Copy down
> --
> Max
> Singapore
> ---
```
 0

6 Replies
229 Views

Similiar Articles:

7/25/2012 3:18:33 PM