Lookup Formula

[Scott Frickman]

In Sheet 1

Row 147 Col’s C to IV are numbers between 0 and 17
Rows 150 through 165 is the target formula location


In Sheet 2
Row 99 Col’s B through R are numbers from 1 to 17 i.e. B99=1, C99=2,
D99=3 etc to R99=17
Rows 100 through 155 Col’s B through R have data numbers

What I need is a formula for sheet1 rows 150 through 165 that match
the number in row 147 with the number in Sheet2 brining the data in
Sheet2 to Sheet1


Here’s and example:

Sheet1 col=> C D E F G
Row 147 0 0 1 2 3
150 15 5 18
151 18 4 20



Sheet2 col=> B C D E F
Row 99 0 0 1 2 3
Row 100 10 20 15 5 18
Row 101 2 4 18 4 20


So in the example the formula in rows 150 through 165 match the number
in Sheet1 row 147 with the same number in Sheet2 row 99 and then takes
the number in rows 100 through 155 to Sheet1 rows 150 to 165.



I think that about explains it.In Sheet 1

Row 147 Col’s C to IV are numbers between 0 and 17
Rows 150 through 165 is the target formula location


In Sheet 2
Row 99 Col’s B through R are numbers from 1 to 17 i.e. B99=1, C99=2,
D99=3 etc to R99=17
Rows 100 through 155 Col’s B through R have data numbers

What I need is a formula for sheet1 rows 150 through 165 that match
the number in row 147 with the number in Sheet2 brining the data in
Sheet2 to Sheet1


Here’s and example:

Sheet1 col=> C D E F G
Row 147 0 0 1 2 3
150 15 5 18
151 18 4 20



Sheet2 col=> B C D E F
Row 99 0 0 1 2 3
Row 100 10 20 15 5 18
Row 101 2 4 18 4 20


So in the example the formula in rows 150 through 165 match the number
in Sheet1 row 147 with the same number in Sheet2 row 99 and then takes
the number in rows 100 through 155 to Sheet1 rows 150 to 165.



I think that about explains it.

 

[Bernard V Liengme]

Hi Scott,
I think I understand what you want but have trouble with the 0's in the
first two columns.
In Sheet1 cell E150 =HLOOKUP($E147,Sheet2!$B$99:$R$101,2,FALSE). Copy this
down to row 151, edit the formula to read
=HLOOKUP($E147,Sheet2!$B$99:$R$101,3,FALSE). Now copy E150:F150 across to
column IV. This is using your sample data on three rows. Let's get that
working first.

Now to get it to work up to row 165. We need to extent the lookup_array to
R155. To save having to modify the 3 parameter in the lookup formula, start
in E150 with =HLOOKUP($E147,Sheet2!$B$99:$R$155,ROW()-149,FALSE)

Bernard

 

[SF]

Bernard,

Thanks for the formula. I made a mistake in the information provided.
The data set in Sheet2 is Row 99 through Row 115 not 155.

In Sheet1 there are many reference cells with 0 in row 147.

Making the correction to your formula in Sheet 1 cell E150 on my
spreadsheet I entered:

=HLOOKUP($E147,Data_WRKSHT!$B$99:$R$115,ROW()-149,FALSE)

The formula returned the #NA.

Guess I am doing something wrong.

 

[SF]

Hi Bernard,

Having recheck the formula I made a correction so it reads as follows:

HLOOKUP(E$147,Data_WRKSHT!$B$99:$R$115,ROW()-149,FALSE)

So, it should have been E$147 no $E147.

Copying that down the col and across the rows gave me the correct
answers except where the reference cell is 0 in row 147. There I get
the #NA error. I suppose I could add an If statement in front of the
formula so that if _147 = 0 return 0 otherwise do the lookup.

Is that the best way or is there another?