VLookup

[SavageMind]

Hey guys long time. I have created a spreadsheet that uses VLOOKUP to find age and weight data on a another hidden sheet compare it to the the age in the previous field and show the proper weight for that age. It originally looked up data from a outside source. I have tried to clean up the formula but well it still works referencing a non existing source but won't with the existing source.

Here is the formula: =IF(OR(AA3="",AA3<17,AB3=""),"",VLOOKUP(AB3,'F:\BU\My Documents 02Nov06\Matrix\[ALL PERSONEL.xls]w'!$A$5:$E$27,IF(AA3>40,11,ROUNDUP((AA3-16)/5+1,0)),TRUE)).

The issue is it is supposed to take the age from AA and height from AB compare it to a table in Sheet4 and the show the proper weight for that height and age. It works regardless of the none existent reference EXCEPT it won't show proper weight above 39 and I wanted to be able to reference C so I could include women since they are a different scale but on the same reference sheet. I made this 3 yrs ago and have now just pulled it out and when I made the original formula it was with time and honestly a book I no longer have. Any help would be great.

This is the file since the attachments won't work for me. I have updated the URL to the exhisting version of the file. Feel free to use it yourself but give credit where it is due.

http://www.mediafire.com/?mdmgnwmknmj

[daddylonglegs]

I can't view your sheet but I note that values of AA3 of > 36 will fail because such values will either generate column index of 6 or 11 and your lookup range is only 5 columns ($A$5:$E$27)

If you need up to column 11 then change to $A$5:$K$27

[SavageMind]

I appreciate your help, but it did not work. I posted the new url to the updated file. I'm at a complete loss. I normally fiddle until it works. I just keep hittinh a dead end. Thanks.

[teylyn]

does it really require a 1MB file to illustrate your issue? can't you delete the irrelevant stuff and upload a smaller version of the file? zipping it will also further reduce the size.

[PMO]

Hello,

My level of English is very low and I hope you understand me.
I do not quite understand your problem, but perhaps a solution with a custom function.

Copy the code below into a standard module.

Please Login or Register  to view this content.

PROCEDURE
The function has 3 arguments
Function PROPERWEIGHT (Age As Variant, Height As Variant, Optional Gender = MALE As Variant) As Variant
Age: enter a number OR a cell address
Height: enter a number OR a cell address
Gender: enter M or F (you can change the constants Const MALE As String = "xxx" Const FEMALE As String = "yyy") OR a cell address

EXAMPLE
PROPERWEIGHT = (AA2, AB2, "M") or PROPERWEIGHT = (28, 65, "m") or PROPERWEIGHT = (AA2, AB2, C2) where C2 contains either F or H.

I hope I was clear.

I put your workbook as an attachment in which I have removed the worksheets that were not needed.

Best regards.

PMO
Patrick Morange

[SavageMind]

Patrick. Sorry for responding so late. That worked perfectly. Thank you for the help.

[PMO]

Hello SavageMind,
It's not so late, it's antediluvian.
Happy good year 2011 and happy good year 2012.

PMO
Patrick Morange