|
I'm confused with how I should program this vlookup formula. Here's the gist: I have 4 arrays for each age/ gender group. 5 age groups, for a total of 10 arrays. They are all labeled, GXXZ, where XX is the age group variable (30, 40, 50, 60, 61), and Z is the variable for each array (A, R, P, S), and G is gender notation (M/F) So I can have variables such as M30R, or F60P. This comes out to 40 total arrays, with up to 20 rows (all have two columns). I use the formula =vlookup(target value,array,2,True) to go from the value to the point system. Target value are my A, R, P, S variables. Problem is I have 50 people, who get older, and get taken off list/ added to the list. So I wanted to add a second function to change the array value (M30R) so it updates when I change it from 30 years old to 31 years old. I created 8 new arrays that consist of the other 40 arrays. 4 based off female gender, 4 off male gender each subparts of A, R, P, S. The arrays look like this 30 M30A 40 M40A 50 M50A 60 M60A 61 M61A That would be array "MA" I then created a new lookup that is written like this =vlookup(age,MA,2,True). This works in the way that it spits out the proper MXXA array based off age. I try to nest it such as =vlookup(A_target_value,vlookup(age,MA,2,True),2,True). So that it calculates the appropriate array based off age and then spits out appropriate point value based off target value. This provides error n#a. Which doesn't make sense because the individual arrays (to calculate MXXA, and point value work. I've also tried to direct the =vlookup(A_target_value,array,2,True) formula to the result of a cell that only consists of =vlookup(age,MA,2,True), simply replacing "array" with b2. But it gives same error. Yet the direct link to the MXXA array works. I've also considered using VBA to create if, then statements that replace the XX part of the vlookup formula based off age, and the Z part based off gender but I'd have to figure this out (as I haven't done VBA is a long time). I probably should have brought this home from work, but I forgot to save it to my email. I hope this doesn't sound all 
|
#
¿
Jul 21, 2011 00:21
|
|
|
|
| # ¿ May 14, 2024 12:58 |
|
|
esquilax posted:I'm having a hard time parsing your post, but it looks like you're trying to use a value that you either pointed to or did a vlookup to as a named range. If that's the case, you need to use the indirect() function to change it from a text string to a "named range" string. As in, maybe I just had this explained to me by someone else "Because the nested vlookup is only going to return a single value, not an array. There are other functions if you need to dynamically calculate arrays."
|
|
#
¿
Jul 21, 2011 00:49
|
|
|
esquilax posted:I'm having a hard time parsing your post, but it looks like you're trying to use a value that you either pointed to or did a vlookup to as a named range. If that's the case, you need to use the indirect() function to change it from a text string to a "named range" string. As in, It actually worked. Thank you. I have another problem now though. Those four values needed to be added up. But if one of the target values doesn't have a value (either exempt or blank field) I need the calculated value (vlookup) to be a specific amount. I've tried making a field in my array such that: 0 0 1 2 25 40 exempt 60 But it gives me an error, no matter if pu the exempt value at top or bottom. I've tried this: =IF(H3=exempt,"60",VLOOKUP(H3,INDIRECT(VLOOKUP(B3,MR,2,TRUE)),2,TRUE)) and =IF(H3=exempt,"60",INDIRECT(VLOOKUP(H3,INDIRECT(VLOOKUP(B3,MR,2,TRUE)),2,TRUE))) Both give the error #NAME? B3 is pointing to age, MR is the approriate array, H3 is the time. My other option would be to create the value that exempt=x value, but I'd need 4 values and 4 different exempt. EDIT: This is my solution: I created 4 new rows for the data that are in a different location (off view) in each they have =if(target_value=0,needed point value,cell_with_vlookup_data) HClChicken fucked around with this message at 01:36 on Jul 22, 2011 |
|
#
¿
Jul 21, 2011 17:44
|
|