Hlookup or vlookup help

[Guest]

Hi to all
my spreadsheet is laid out as follows
A1:A20 production line names
b1:Y20 hourly production for each line
B21:Y21 Total production
Z1:Z20 I want the plant production for the peak hour

Im trying to piece together a formula not sure vlookup or hlookup using
MATCH(MAX(B21:Y21),B1:Y20,1) but it keeps giving me a #N/A for some reason. What am I doing wrong, and how will I incorporate this into the lookup formula

Many thanks

 

[Domenic]

Hi Shelly,

If I understand you correctly, try,

=INDEX($B$1:$Y$1,MATCH(MAX($B$21:$Y$21),$B$21:$Y$21,0))

Hope this helps!

 

[Stephen Dunn]

Hi Shelly,

If I'm reading you right, you need this in Z1:

=index($b1:$y1,match(max($b$21:$y$21),$b$21:$y$21,0))

copied through to Z20.

The reason that your formula is returning #N/A is that it must be given a
single row (or column) reference to search within. However, even if you had
restricted your search range to a single row from the main data, how would
it have found a *total* within that range?


HTH
Steve D.

Hi to all
my spreadsheet is laid out as follows
A1:A20 production line names
b1:Y20 hourly production for each line
B21:Y21 Total production
Z1:Z20 I want the plant production for the peak hour

Im trying to piece together a formula not sure vlookup or hlookup using
MATCH(MAX(B21:Y21),B1:Y20,1) but it keeps giving me a #N/A for some

reason. What am I doing wrong, and how will I incorporate this into the
lookup formula

 

[RagDyeR]

You're correctly getting the error, because your formula is trying to find a
number that *doesn't* exist in the data list.
Row 21 is your *totals*, so *no single* hour's production will match any of
the numbers in that row.

Don't understand exactly what you're looking for.
<<"Z1:Z20 I want the plant production for the peak hour">>
Isn't that the MAX(B21:Y21) ???

OR, are you looking for the *HOUR* of peak production?
=MATCH(MAX(B21:Y21),B21:Y21,0)

BUT, that doesn't explain what you want to place in column Z.

Do you want the peak *hour* for each *individual* production line?
Then, same formula as above, just change references!
In Z1 enter:
=MATCH(MAX(B2:Y2),B2:Y2,0)
And copy down.

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi to all
my spreadsheet is laid out as follows
A1:A20 production line names
b1:Y20 hourly production for each line
B21:Y21 Total production
Z1:Z20 I want the plant production for the peak hour

Im trying to piece together a formula not sure vlookup or hlookup using
MATCH(MAX(B21:Y21),B1:Y20,1) but it keeps giving me a #N/A for some reason.
What am I doing wrong, and how will I incorporate this into the lookup
formula

Many thanks

 

[RagDyeR]

Check your clock.
It might be *slow*!

Hi Shelly,

If I understand you correctly, try,

=INDEX($B$1:$Y$1,MATCH(MAX($B$21:$Y$21),$B$21:$Y$21,0))

Hope this helps!

 

[Domenic]

Check your clock.
It might be *slow*!

Yep! Three minutes slow. Thanks!

 

[Guest]

Ok I see the error of my ways thanks for your help!!!!!