Vlookup and Hlookup equation

[SGT Buckeye]

Can someone please explain the following formula to me?

=IF(B4>"A",(IF(E4>0,VLOOKUP(E4,PUDATA,(IF(B4="M",(HLOOKUP(C4,PUAGE,2)),
(HLOOKUP(C4,PUAGE,2)+1)))),0))," ")

It is taken from cell F4 on the "Input Data" worksheet located at this
URL:

http://www.armystudyguide.com/content/bm~doc/apft-scorecard-in-mass.xls

I know how to write Vlookups and this worksheet works fine. I am just
curious to know how the combination of Vlookup and Hlookup works in
case I need to modify some of the scoring parameters in the future.
Thank you in advance for your help.

 

[Don Guillett]

I think I would have written the formula a bit differently
hlookup(c4,puage,2)+if(b4="m",1,0)
but it appears to be doing an hlookup to find which column to return from
the pudata range.
=IF(OR(B4>"A",E4>0),VLOOKUP(E4,PUDATA,HLOOKUP(C4,PUAGE,2)+IF(B4="m",0,1)),"")
Perhaps b4>"A" should be D4>"A" or ???

 

[AKphidelt]

It's saying, if B4 is Male/Female, and if E4 has more then 0 Push Ups, then
VLOOKUP the push up number in the PUDATA which is probably the data on the
Push-Up sheet.

So then, if the person is a male, look up the Age of the person, in the
Push-Up sheet (The PUAGE name is probably set up using Range (B91:T92) of the
Push Up sheet).

So this HLOOKUP returns the Column number for the Vlookup of the number of
Push ups the person does.

So for F4 as the example... it goes in this order...

1) Is the person a Male or Female? TRUE, so...
2) Did the person do more then 0 Push Ups? TRUE, so...
3) VLOOKUP 15 (# of Push ups), using the PUDATA Array (most likely the Push
Ups Sheet). So find the #15 in column A... then
4) Is the person Male or Female? Male, so...
5) HLOOKUP the persons Age, go down to the PUAGE Array (Bottom of Push Ups)
and
6) Find the persons age and give the value... in this case the value is 18
7) So in the end it looks like, since the person is a male and did more then
0 push ups, look up the number of Push Ups and go over 18 columns, and the
score should be 57.

If the person were female it would do the exact same thing except take a
value that is 1 more then the males value, as in it would use column 19
instead of column 18. I could give more detail but the web site won't let me
enter the formula or view the names of the ranges... like Im just guessing
what the PUAGE range is.

 

[SGT Buckeye]

I think I would have written the formula a bit differently
hlookup(c4,puage,2)+if(b4="m",1,0)
but it appears to be doing an hlookup to find which column to return from
the pudata range.
=IF(OR(B4>"A",E4>0),VLOOKUP(E4,PUDATA,HLOOKUP(C4,PUAGE,2)+IF(B4="m",0,1)),"­")
Perhaps b4>"A" should be D4>"A" or ???
--
Don Guillett
SalesAid Software







- Show quoted text -

Don, thank you for your response. This formula works great and seems
much cleaner. It is definitely easier for me to understand. Thanks
again.

 

[SGT Buckeye]

It's saying, if B4 is Male/Female, and if E4 has more then 0 Push Ups, then
VLOOKUP the push up number in the PUDATA which is probably the data on the
Push-Up sheet.

So then, if the person is a male, look up the Age of the person, in the
Push-Up sheet (The PUAGE name is probably set up using Range (B91:T92) of the
Push Up sheet).

So this HLOOKUP returns the Column number for the Vlookup of the number of
Push ups the person does.

So for F4 as the example... it goes in this order...

1) Is the person a Male or Female? TRUE, so...
2) Did the person do more then 0 Push Ups? TRUE, so...
3) VLOOKUP 15 (# of Push ups), using the PUDATA Array (most likely the Push
Ups Sheet). So find the #15 in column A... then
4) Is the person Male or Female? Male, so...
5) HLOOKUP the persons Age, go down to the PUAGE Array (Bottom of Push Ups)
and
6) Find the persons age and give the value... in this case the value is 18
7) So in the end it looks like, since the person is a male and did more then
0 push ups, look up the number of Push Ups and go over 18 columns, and the
score should be 57.

If the person were female it would do the exact same thing except take a
value that is 1 more then the males value, as in it would use column 19
instead of column 18. I could give more detail but the web site won't let me
enter the formula or view the names of the ranges... like Im just guessing
what the PUAGE range is.





- Show quoted text -

AKphidelt, thank you for the response. Your post is exactly what I
was looking for.

 

[SGT Buckeye]

I think I would have written the formula a bit differently
hlookup(c4,puage,2)+if(b4="m",1,0)
but it appears to be doing an hlookup to find which column to return from
the pudata range.
=IF(OR(B4>"A",E4>0),VLOOKUP(E4,PUDATA,HLOOKUP(C4,PUAGE,2)+IF(B4="m",0,1)),"­")
Perhaps b4>"A" should be D4>"A" or ???
--
Don Guillett
SalesAid Software







- Show quoted text -

I was wondering if you could posibly simplify this formula as well?

=IF(B4>"A",IF(I4>0,IF(VLOOKUP(I4,DATA2MI,
1)<I4,VLOOKUP(I4+0.00007,DATA2MI,(IF(B4="M",(HLOOKUP(C4,MIAGE,2)),
(HLOOKUP(C4,MIAGE,2)+1)))),VLOOKUP(I4,DATA2MI,(IF(B4="M",
(HLOOKUP(C4,MIAGE,2)),(HLOOKUP(C4,MIAGE,2)+1))))),0)," ")

 

[SGT Buckeye]

I was wondering if you could posibly simplify this formula as well?

=IF(B4>"A",IF(I4>0,IF(VLOOKUP(I4,DATA2MI,
1)<I4,VLOOKUP(I4+0.00007,DATA2MI,(IF(B4="M",(HLOOKUP(C4,MIAGE,2)),
(HLOOKUP(C4,MIAGE,2)+1)))),VLOOKUP(I4,DATA2MI,(IF(B4="M",
(HLOOKUP(C4,MIAGE,2)),(HLOOKUP(C4,MIAGE,2)+1))))),0)," ")- Hide quoted text -

- Show quoted text -

Dan, using your formula above as a guide, I was able to redo the
formulas for sit-ups and the 2 mile run. Thanks for the assistance.

 

[Don Guillett]

Had you just used
17 22 27 32 37 42 47 52 57 62

vs
17-21 22-26 27-31 32-36 37-41 42-46 47-51 52-56 57-61
62+

then you could have used a simpler formula. Name ranges to suit but do NOT
put a - in a tab name. Use _ or a space instead.
=IF(E23<1,"",VLOOKUP(E23,PUSHUP!$A$1:$AS$88,MATCH(C23,PUSHUP!$A$1:$U$1)+IF(B23="m",0,1)))
=IF(E23<1,"",VLOOKUP(E23,PUdata,MATCH(C23,PUrow1)+IF(B23="m",0,1)))
BTW, what is this for and for who?
--
Don Guillett
SalesAid Software
(e-mail address removed)

I was wondering if you could posibly simplify this formula as well?

=IF(B4>"A",IF(I4>0,IF(VLOOKUP(I4,DATA2MI,
1)<I4,VLOOKUP(I4+0.00007,DATA2MI,(IF(B4="M",(HLOOKUP(C4,MIAGE,2)),
(HLOOKUP(C4,MIAGE,2)+1)))),VLOOKUP(I4,DATA2MI,(IF(B4="M",
(HLOOKUP(C4,MIAGE,2)),(HLOOKUP(C4,MIAGE,2)+1))))),0)," ")- Hide quoted
text -

- Show quoted text -

Dan, using your formula above as a guide, I was able to redo the
formulas for sit-ups and the 2 mile run. Thanks for the assistance.

 

[SGT Buckeye]

Had you just used
17 22 27 32 37 42 47 52 57 62

vs
17-21 22-26 27-31 32-36 37-41 42-46 47-51 52-56 57-61
62+

then you could have used a simpler formula. Name ranges to suit but do NOT
put a - in a tab name. Use _ or a space instead.
=IF(E23<1,"",VLOOKUP(E23,PUSHUP!$A$1:$AS$88,MATCH(C23,PUSHUP!$A$1:$U$1)+IF(­B23="m",0,1)))
=IF(E23<1,"",VLOOKUP(E23,PUdata,MATCH(C23,PUrow1)+IF(B23="m",0,1)))
BTW, what is this for and for who?
--
Don Guillett
SalesAid Software






Dan, using your formula above as a guide, I was able to redo the
formulas for sit-ups and the 2 mile run. Thanks for the assistance.- Hide quoted text -

- Show quoted text -

This is for my Army unit. It will be used to calculate soldiers
scores on the Army Physical Fitness Test. Normally, we have to do
this manually by looking at a sheet and finding the correct
information. As you can imagine, this is quite time consuming when
you have an entire unit to score. This spreadsheet will cut down the
time it takes to compile scores.

 

[Don Guillett]

As an ex USAF officer, I'm glad to help. I was once a Hq Co CO so am
familiar.
A good effort but it could have been easier using better design.

--
Don Guillett
SalesAid Software
(e-mail address removed)

Had you just used
17 22 27 32 37 42 47 52 57 62

vs
17-21 22-26 27-31 32-36 37-41 42-46 47-51 52-56
57-61
62+

then you could have used a simpler formula. Name ranges to suit but do NOT
put a - in a tab name. Use _ or a space instead.
=IF(E23<1,"",VLOOKUP(E23,PUSHUP!$A$1:$AS$88,MATCH(C23,PUSHUP!$A$1:$U$1)+IF(­B23="m",0,1)))
=IF(E23<1,"",VLOOKUP(E23,PUdata,MATCH(C23,PUrow1)+IF(B23="m",0,1)))
BTW, what is this for and for who?
--
Don Guillett
SalesAid Software
in message






Dan, using your formula above as a guide, I was able to redo the
formulas for sit-ups and the 2 mile run. Thanks for the assistance.- Hide
quoted text -

- Show quoted text -

This is for my Army unit. It will be used to calculate soldiers
scores on the Army Physical Fitness Test. Normally, we have to do
this manually by looking at a sheet and finding the correct
information. As you can imagine, this is quite time consuming when
you have an entire unit to score. This spreadsheet will cut down the
time it takes to compile scores.

 

[SGT Buckeye]

As an ex USAF officer, I'm glad to help. I was once a Hq Co CO so am
familiar.
A good effort but it could have been easier using better design.

--
Don Guillett
SalesAid Software








This is for my Army unit. It will be used to calculate soldiers
scores on the Army Physical Fitness Test. Normally, we have to do
this manually by looking at a sheet and finding the correct
information. As you can imagine, this is quite time consuming when
you have an entire unit to score. This spreadsheet will cut down the
time it takes to compile scores.- Hide quoted text -

- Show quoted text -

I modified the worksheet that contained the run time and
points earned data to include headers in the pattern M17, F17, M18,
F18. I then sorted the information in descending order by run time
(see table below).

Time M17 F17 M18 F18
26:36 0 0 0 0
26:30 0 0 0 0
26:24 0 0 0 0
26:18 0 0 0 0


I then used the following formula using a double lookup:


=IF(ISBLANK($J4)," ",IF($J4>=RUN!A2,0,IF($J4<=RUN!
A138,100,OFFSET(RUN!
$A$1,MATCH($J4,RUN!$A$2:$A$140,-1),MATCH($E4,RUN!$B$1:$CO$1,0)))))

Thanks for all the help.