Lookup tables??

[damian_r]

Hi,

i hope somebody can help with this today as i'm finding this a hard one and i don't know how to acheive it.

On sheet 1, cells Z4 to Z20 may have any of the following typed into it.
A
B
C
PFC
ASB
Hollow
RSA
Flat
Tee
Bearing Piles

the following is using cell Z4 as an example.

Depending what is typed in Z4, the cells to the right starting at AA4 and going to AN4 need to copy the values entered into a table on sheet 2 as follws:-

On sheet two, cell K5 contains the text A, cell K6 contains B, cell K7 contains C etc etc (this continues down to K14 containing bearing piles). To the right of this from cells M5 to Z7 are ethier empty of contain the letter L.

so, if Z4 on sheet 1 contains RSA, i want the cells in teh range AA4 to AN4 to look for teh cell which contains the word RSA from the range of K5 to K14 on sheet 2 and replace it's content with that from the range of M11to Z11 from sheet 2 (seeing as it's cell K11 on sheet 2 which contains the value RSA).


Hope this makes some sort of sense.


D

 

[sanrv1f]

Hope I understood your requirement

just give 3,4,5,...16 as titles on row 3 to your data on Sheet1 Column AA to Column AN, and use the below formula in the range AA4:AN20

=VLOOKUP($Z4,Sheet2!$K$5:$Z$14,AA$3,0)

or

give the same titles to the data on row 4 to Sheet2 column M to Column Z

and use

=VLOOKUP($Z4,Sheet2!$K$5:$Z$14,MATCH(AA$3,Sheet2!$K$4:$Z$4,),0)