Lookups...

[ajayb]

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

 

[Pete_UK]

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

 

[ajayb]

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]

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

Pete