# Lookup tables??

#### damian_r

##### Active Member
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

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### sanrv1f

##### MrExcel MVP
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)

Replies
1
Views
673
Replies
5
Views
310
Replies
16
Views
901
Replies
18
Views
612
Replies
0
Views
531

1,191,397
Messages
5,986,359
Members
440,020
Latest member
IfsandSums

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back