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.

Use FALSE as the 4th argument

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"007juk" wrote in message ...
|
| 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.

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


"007juk" wrote in message ...
|
| Niek Otten;581677 Wrote:
| Use FALSE as the 4th argument
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "007juk" wrote in message
| ...
| |
| | 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

=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,

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


"007juk" wrote in message ...
|
| =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
|
|
| "007juk" wrote in message
| ...
| |
| | Niek Otten;581677 Wrote:
| | Use FALSE as the 4th argument
| |
| | --
| | Kind regards,
| |
| | Niek Otten
| | Microsoft MVP - Excel
| |
| | "007juk" wrote in message
| | ...
| | |
| | | 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

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

On Nov 7, 10:29 pm, 007juk wrote:
=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

"007juk" wrote in message
...
|
| Niek Otten;581677 Wrote:
| Use FALSE as the 4th argument
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "007juk" wrote in message
| ...
| |
| | 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 -

Aha!. Indeed if you need multiple results, look he

http://office.microsoft.com/en-us/ex...260381033.aspx

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Pete_UK" wrote in message oups.com...
| 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
|
| On Nov 7, 10:29 pm, 007juk wrote:
| =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
|
| "007juk" wrote in message
| ...
| |
| | Niek Otten;581677 Wrote:
| | Use FALSE as the 4th argument
| |
| | --
| | Kind regards,
| |
| | Niek Otten
| | Microsoft MVP - Excel
| |
| | "007juk" wrote in message
| | ...
| | |
| | | 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 -
|
|