Question re Vlookup function within Excel.

0

007juk

Hi,

Can the following be done.

I have the following formula in Sheet1 of my workbook.

=VLOOKUP(B6,Sheet2!A2:BC499,5,)

When cell A2 of Sheet2 is populated with the name John and cell E2
contains 'ABC' the formula works ok, but when cell A3 is populated with
the same name, but cell E3 has 'DEF' it won't display the text 'DEF'.
Can this be done so that each time I enter the same name in column A
with different data in column E it populates correctly.

Many thanks.
 
N

Niek Otten

Use FALSE as the 4th argument

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| Hi,
|
| Can the following be done.
|
| I have the following formula in Sheet1 of my workbook.
|
| =VLOOKUP(B6,Sheet2!A2:BC499,5,)
|
| When cell A2 of Sheet2 is populated with the name John and cell E2
| contains 'ABC' the formula works ok, but when cell A3 is populated with
| the same name, but cell E3 has 'DEF' it won't display the text 'DEF'.
| Can this be done so that each time I enter the same name in column A
| with different data in column E it populates correctly.
|
| Many thanks.
|
|
|
|
| --
| 007juk
 
0

007juk

Niek said:
Use FALSE as the 4th argument

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| Hi,
|
| Can the following be done.
|
| I have the following formula in Sheet1 of my workbook.
|
| =VLOOKUP(B6,Sheet2!A2:BC499,5,)
|
| When cell A2 of Sheet2 is populated with the name John and cell E2
| contains 'ABC' the formula works ok, but when cell A3 is populated
with
| the same name, but cell E3 has 'DEF' it won't display the text
'DEF'.
| Can this be done so that each time I enter the same name in column A
| with different data in column E it populates correctly.
|
| Many thanks.
|
|
|
|
| --
| 007juk
Click to expand...

Unfortunately, it is still displaying ABC when I wanted it to display
DEF which is entered in Cell E3. I don't think vlookup is going to
work. I habe also tried index/match function, but that doesn't work
either.

Thanks for your reply.... I'm puzzled with this one.
 
N

Niek Otten

Please post your formula, your input data and the relevant part of the lookup table.
Do you have multiple Johns?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|
| Niek Otten;581677 Wrote:
| > Use FALSE as the 4th argument
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | > |
| > | Hi,
| > |
| > | Can the following be done.
| > |
| > | I have the following formula in Sheet1 of my workbook.
| > |
| > | =VLOOKUP(B6,Sheet2!A2:BC499,5,)
| > |
| > | When cell A2 of Sheet2 is populated with the name John and cell E2
| > | contains 'ABC' the formula works ok, but when cell A3 is populated
| > with
| > | the same name, but cell E3 has 'DEF' it won't display the text
| > 'DEF'.
| > | Can this be done so that each time I enter the same name in column A
| > | with different data in column E it populates correctly.
| > |
| > | Many thanks.
| > |
| > |
| > |
| > |
| > | --
| > | 007juk
|
| Unfortunately, it is still displaying ABC when I wanted it to display
| DEF which is entered in Cell E3. I don't think vlookup is going to
| work. I habe also tried index/match function, but that doesn't work
| either.
|
| Thanks for your reply.... I'm puzzled with this one.
|
|
|
|
| --
| 007juk
 
0

007juk

=VLOOKUP(B6,Sheet2!A2:BC499,5,)

B6 is on Sheet1 and contains the name, I have a list of 140 names an
I'm using data validation to select. Within Sheet2 I have 10 columns
but at the moment the cols I'm interested in are ColA and ColE. Col
contains the name and colB contains the data I want to display.

If sheet2 is populated with the following A2 contains John, A3 contain
Mike and A4 contains John and colE has been filled with data then I wan
to select john from the drop down in sheet1 and for cells B10 and B11 t
be populated with data. If I select Mike from the drop down then cel
B10 of sheet1 would be populated with data.

Many thanks,
 
N

Niek Otten

As I posted before, use FALSE as the 4th argument. Look in HELP or use the function wizard; that could have saved you days!

=VLOOKUP(B6,Sheet2!A2:E499,5,FALSE)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|
| =VLOOKUP(B6,Sheet2!A2:BC499,5,)
|
| B6 is on Sheet1 and contains the name, I have a list of 140 names and
| I'm using data validation to select. Within Sheet2 I have 10 columns,
| but at the moment the cols I'm interested in are ColA and ColE. Col A
| contains the name and colB contains the data I want to display.
|
| If sheet2 is populated with the following A2 contains John, A3 contains
| Mike and A4 contains John and colE has been filled with data then I want
| to select john from the drop down in sheet1 and for cells B10 and B11 to
| be populated with data. If I select Mike from the drop down then cell
| B10 of sheet1 would be populated with data.
|
| Many thanks,
|
|
|
|
|
|
|
|
| Niek Otten;582366 Wrote:
| > Please post your formula, your input data and the relevant part of the
| > lookup table.
| > Do you have multiple Johns?
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| >
| > | > |
| > | Niek Otten;581677 Wrote:
| > | > Use FALSE as the 4th argument
| > | >
| > | > --
| > | > Kind regards,
| > | >
| > | > Niek Otten
| > | > Microsoft MVP - Excel
| > | >
| > | > | > | > |
| > | > | Hi,
| > | > |
| > | > | Can the following be done.
| > | > |
| > | > | I have the following formula in Sheet1 of my workbook.
| > | > |
| > | > | =VLOOKUP(B6,Sheet2!A2:BC499,5,)
| > | > |
| > | > | When cell A2 of Sheet2 is populated with the name John and cell
| > E2
| > | > | contains 'ABC' the formula works ok, but when cell A3 is
| > populated
| > | > with
| > | > | the same name, but cell E3 has 'DEF' it won't display the text
| > | > 'DEF'.
| > | > | Can this be done so that each time I enter the same name in
| > column A
| > | > | with different data in column E it populates correctly.
| > | > |
| > | > | Many thanks.
| > | > |
| > | > |
| > | > |
| > | > |
| > | > | --
| > | > | 007juk
| > |
| > | Unfortunately, it is still displaying ABC when I wanted it to
| > display
| > | DEF which is entered in Cell E3. I don't think vlookup is going to
| > | work. I habe also tried index/match function, but that doesn't work
| > | either.
| > |
| > | Thanks for your reply.... I'm puzzled with this one.
| > |
| > |
| > |
| > |
| > | --
| > | 007juk
|
|
|
|
| --
| 007juk
 
P

Pete_UK

If you have multiple "John"'s, then VLOOKUP (and MATCH) will only find
the first occurrence. You need to approach it in a different way.

Pete
 
N

Niek Otten

Aha!. Indeed if you need multiple results, look here:

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

Takes a bit of time, but if you follow the instructions carefully, it works beautifully

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| If you have multiple "John"'s, then VLOOKUP (and MATCH) will only find
| the first occurrence. You need to approach it in a different way.
|
| Pete
|
| > =VLOOKUP(B6,Sheet2!A2:BC499,5,)
| >
| > B6 is on Sheet1 and contains the name, I have a list of 140 names and
| > I'm using data validation to select. Within Sheet2 I have 10 columns,
| > but at the moment the cols I'm interested in are ColA and ColE. Col A
| > contains the name and colB contains the data I want to display.
| >
| > If sheet2 is populated with the following A2 contains John, A3 contains
| > Mike and A4 contains John and colE has been filled with data then I want
| > to select john from the drop down in sheet1 and for cells B10 and B11 to
| > be populated with data. If I select Mike from the drop down then cell
| > B10 of sheet1 would be populated with data.
| >
| > Many thanks,
| >
| > Niek Otten;582366 Wrote:
| >
| >
| >
| >
| >
| > > Please post your formula, your input data and the relevant part of the
| > > lookup table.
| > > Do you have multiple Johns?
| >
| > > --
| > > Kind regards,
| >
| > > Niek Otten
| > > Microsoft MVP - Excel
| >
| > >| > > |
| > > | Niek Otten;581677 Wrote:
| > > | > Use FALSE as the 4th argument
| > > | >
| > > | > --
| > > | > Kind regards,
| > > | >
| > > | > Niek Otten
| > > | > Microsoft MVP - Excel
| > > | >
| > > | >| > > | > |
| > > | > | Hi,
| > > | > |
| > > | > | Can the following be done.
| > > | > |
| > > | > | I have the following formula in Sheet1 of my workbook.
| > > | > |
| > > | > | =VLOOKUP(B6,Sheet2!A2:BC499,5,)
| > > | > |
| > > | > | When cell A2 of Sheet2 is populated with the name John and cell
| > > E2
| > > | > | contains 'ABC' the formula works ok, but when cell A3 is
| > > populated
| > > | > with
| > > | > | the same name, but cell E3 has 'DEF' it won't display the text
| > > | > 'DEF'.
| > > | > | Can this be done so that each time I enter the same name in
| > > column A
| > > | > | with different data in column E it populates correctly.
| > > | > |
| > > | > | Many thanks.
| > > | > |
| > > | > |
| > > | > |
| > > | > |
| > > | > | --
| > > | > | 007juk
| > > |
| > > | Unfortunately, it is still displaying ABC when I wanted it to
| > > display
| > > | DEF which is entered in Cell E3. I don't think vlookup is going to
| > > | work. I habe also tried index/match function, but that doesn't work
| > > | either.
| > > |
| > > | Thanks for your reply.... I'm puzzled with this one.
| > > |
| > > |
| > > |
| > > |
| > > | --
| > > | 007juk
| >
| > --
| > 007juk- Hide quoted text -
| >
| > - Show quoted text -
|
|
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

EXCEL - IF(IFERROR(VLOOKUP question 0
Vlookup and return sheet name also 2
vlookup column based on user input 5
Use vlookup within offset? 4
use of ADDRESS function within CELL function 4
RELATIVE REFERENCE WHEN MATCHING 2 PRODUCTS 4
First column of range in VLOOKUP contains formula 2
VLOOKUP 1

Top