Ask Your Question
0

I need help in creating a lookup formula from my chart.

asked 2019-06-18 18:29:12 +0200

007Charlette gravatar image

updated 2019-06-18 22:25:40 +0200

So far I have tried to enter my data as a vertical table for length and weight. These are set whole numbers. I did another table for the weight distributions with percentages and the results I have calculated. I am stuck on the simple starting point of having my list recognize the predetermined weights chosen via a drop down option.

I keep getting this error code---508 ---My sheet info is attached--- I have tried a Vlookup =VLOOKUP(Info.A3:a12l,Info.B3:B12,3,0) i think this was the last formula used, i have used so many that were wrong on my end i can't even see straight.

I have attached my file that i am trying to do C:\fakepath\Example.ods

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2019-06-18 19:23:56 +0200

Opaque gravatar image

updated 2019-06-18 22:46:44 +0200

Hello

I'm not sure whether I got it right but I assume the following is what you are looking for: for inches (not including any weight factor)

B17 (Head): =VLOOKUP($B$11;$Info.A$3:O$12;5;0)
B18 (Left Arm): =VLOOKUP($B$11;$Info.A$3:O$12;7;0)
B19 (Right Arm): =VLOOKUP($B$11;$Info.A$3:O$12;9;0)
B20 ((Left Leg): =VLOOKUP($B$11;$Info.A$3:O$12;11;0)
B21 (Right Leg): =VLOOKUP($B$11;$Info.A$3:O$12;13;0)
B22 (Body): =VLOOKUP($B$11;$Info.A$3:O$12;15;0)

for centimeters:
Change $Info.A$3:O$12 to $Info.A$16:O$25 and $B$11 to $B$12

To allow for inches / centimeters you may consider to use IF or IFS function.

Update: Tue Jun 18 22:04:58 CEST 2019: Please see the comment below regarding command argument delimiter on various locales. The lookups above work for locales using a ";" (semicolon).

Update: Tue Jun 18 22:46:07 CEST 2019: See formulas in updated ExampleUpdated.ods C:\fakepath\ExampleUpdated.ods

If the answer is correct or helped you to fix your problem, please click the check mark (✔) next to the answer.

edit flag offensive delete link more

Comments

it is showing an #N/A now which beats a 508 i suppose. I have updated my original to include the spreadsheet part and the new error.

007Charlette gravatar image007Charlette ( 2019-06-18 20:01:45 +0200 )edit

Sorry - but I'm from Germany and my locale has an ";" (semi-colon) as the function argument delimiter and I've sadly tested with my german installation instead of the english (US) one.

If you are on a locale using a "," (comma) as an delimiter, the lookups need to read:

B17 (Head): =VLOOKUP($B$11,$Info.A$3:O$12,5,0)
B18 (Left Arm): =VLOOKUP($B$11,$Info.A$3:O$12,7,0)
B19 (Right Arm): =VLOOKUP($B$11,$Info.A$3:O$12,9,0)
B20 ((Left Leg): =VLOOKUP($B$11,$Info.A$3:O$12,11,0)
B21 (Right Leg): =VLOOKUP($B$11,$Info.A$3:O$12,13,0)
B22 (Body): =VLOOKUP($B$11,$Info.A$3:O$12,15,0)

Opaque gravatar imageOpaque ( 2019-06-18 22:01:25 +0200 )edit

now it is back to showing an error 508

007Charlette gravatar image007Charlette ( 2019-06-18 22:20:25 +0200 )edit

Please tell us your locale - error 508 means that there is an error in parentheses, which makes me assume that ";" (semicolon) might be correct and #N/A would mean: VLOOKUP doesn't find anything, thus it is #N/A (one reason might be that you selected no or an invalid value in B11 and now you have inches in B10 instead of B11, so it is a simple adaption of the formulas above.

Opaque gravatar imageOpaque ( 2019-06-18 22:31:51 +0200 )edit

I have checked your sheet Example.ods: I did nothing tell about using matrix function - You added {=VLOOKUP($B$11;$Info.A$3:O$12;5;0)} and this is wrong. Remove the "Matrix". I've added an updated version of your Example.ods to my answer. Please see above

Opaque gravatar imageOpaque ( 2019-06-18 22:36:12 +0200 )edit

sorry that { } appeared because i used alt, up arrow and shift to enter the vlookup.. I went and typed it as is and pressed enter and it is back to the #N/A error.

Thank you for trying to help figure this out. it has been three days so far and i don't know why or what is wrong with it. I am in the UK , GB,

007Charlette gravatar image007Charlette ( 2019-06-18 23:21:23 +0200 )edit

Thank you! It is fixed!! The last message you sent, I redid and it works.It is fixed. Thank you so much.

007Charlette gravatar image007Charlette ( 2019-06-18 23:30:15 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-06-18 18:29:12 +0200

Seen: 35 times

Last updated: Jun 18