Hi all,

Can you help?

I have the following data on one worksheet called lamps:

A B C D
1 ME4a 1 10 140w Cosmo
2 ME4b 1 8 140w Cosmo
3 S4 1 7 60w Cosmo
4 S5 1 6 55w PLL
5 S6 1 5 55w PLL
6 S5 1 6 60 Cosmo
7 S7 1 5 36w PLL
8 ME3b 2a 10 140w Cosmo
9 ME3c 2a 8 140w Cosmo
10 ME4a 2a 8 60w Cosmo
11 S4 2a 7 60w Cosmo
12 S5 2a 6 55w PLL
13 S5 2a 5 36w PLL
14 S3 2a 6 60w Cosmo
15 S7 2a 5 36w PLL

On another sheet I have the following entry:

A B C D
1 2A 7 S4

In cell D1 I wnat a function that will look for the 3 entries in column A -
C and return the value from column D on the lamps sheet. So the example
above should return 60w Cosmo.

Any help greatly appreciated.

Regards

Andy

I find it easier in a case like this to create a new unique reference
in the lamps sheet - insert a new column D and put this formula in:

=B1&C1&A1

Then copy this down to cover your data.

In D1 of your second sheet you can then enter ths formula:

=VLOOKUP(A1&B1&C1,lamps!D:E,2,0)

to return the corresponding description from what is now column E.

Hope this helps.

Pete

On Jun 16, 4:24*pm, ajayb wrote:
Hi all,

Can you help?

I have the following data on one worksheet called lamps:

* * * * A * * * B * * * C * * * D
1 * * * ME4a * *1 * * * 10 * * *140w Cosmo
2 * * * ME4b * *1 * * * 8 * * * 140w Cosmo
3 * * * S4 * * *1 * * * 7 * * * 60w Cosmo
4 * * * S5 * * *1 * * * 6 * * * 55w PLL
5 * * * S6 * * *1 * * * 5 * * * 55w PLL
6 * * * S5 * * *1 * * * 6 * * * 60 Cosmo
7 * * * S7 * * *1 * * * 5 * * * 36w PLL
8 * * * ME3b * *2a * * *10 * * *140w Cosmo
9 * * * ME3c * *2a * * *8 * * * 140w Cosmo
10 * * *ME4a * *2a * * *8 * * * 60w Cosmo
11 * * *S4 * * *2a * * *7 * * * 60w Cosmo
12 * * *S5 * * *2a * * *6 * * * 55w PLL
13 * * *S5 * * *2a * * *5 * * * 36w PLL
14 * * *S3 * * *2a * * *6 * * * 60w Cosmo
15 * * *S7 * * *2a * * *5 * * * 36w PLL

On another sheet I have the following entry:

* * * * A * * * B * * * C * * * D
1 * * * 2A * * *7 * * * S4 * * *

In cell D1 I wnat a function that will look for the 3 entries in column A -
C and return the value from column D on the lamps sheet. *So the example
above should return 60w Cosmo.

Any help greatly appreciated.

Regards

Andy

Ah, see, sometimes there's a pure and simple way of doing things! I was
getting all tied up with Index and match and whatnot!

Many thanks Pete, that was great.

Andy

"Pete_UK" wrote:

I find it easier in a case like this to create a new unique reference
in the lamps sheet - insert a new column D and put this formula in:

=B1&C1&A1

Then copy this down to cover your data.

In D1 of your second sheet you can then enter ths formula:

=VLOOKUP(A1&B1&C1,lamps!D:E,2,0)

to return the corresponding description from what is now column E.

Hope this helps.

Pete

On Jun 16, 4:24 pm, ajayb wrote:
Hi all,

Can you help?

I have the following data on one worksheet called lamps:

A B C D
1 ME4a 1 10 140w Cosmo
2 ME4b 1 8 140w Cosmo
3 S4 1 7 60w Cosmo
4 S5 1 6 55w PLL
5 S6 1 5 55w PLL
6 S5 1 6 60 Cosmo
7 S7 1 5 36w PLL
8 ME3b 2a 10 140w Cosmo
9 ME3c 2a 8 140w Cosmo
10 ME4a 2a 8 60w Cosmo
11 S4 2a 7 60w Cosmo
12 S5 2a 6 55w PLL
13 S5 2a 5 36w PLL
14 S3 2a 6 60w Cosmo
15 S7 2a 5 36w PLL

On another sheet I have the following entry:

A B C D
1 2A 7 S4

In cell D1 I wnat a function that will look for the 3 entries in column A -
C and return the value from column D on the lamps sheet. So the example
above should return 60w Cosmo.

Any help greatly appreciated.

Regards

Andy

You're welcome, Andy - thanks for feeding back.

Pete

On Jun 16, 7:38*pm, ajayb wrote:
Ah, see, sometimes there's a pure and simple way of doing things! *I was
getting all tied up with Index and match and whatnot!

Many thanks Pete, that was great.

Andy