+ Reply to Thread
Results 1 to 6 of 6

Problem with Hlookup, Vlookup, Index with table

  1. #1
    Registered User
    Join Date
    09-30-2009
    Location
    APG, MD
    MS-Off Ver
    Excel 2008
    Posts
    5

    Problem with Hlookup, Vlookup, Index with table

    Everything I seem to come across in this forum is close, but doesn't exactly answer my question--but close.

    I am trying to re-write an ARMY APFT scale. I have a table, many of the items in this table are similar but not all. The table is A1:K1 and A1:A77 (quite a bit of data) all located on sheet 2. Columns A1:K1 are ages--i.e. 17-21, 22-26, etc., etc.-- Rows A1:A77 are the number of repetitions performed--0 push ups through 77 push ups--you get the idea, I hope. The look up data is based off of the gender, the age, and the number of repetitions. But this data is on sheet 1. How in the world do I do this?

    I figured it should be something like:
    =IF('sheet1'!A7="M", Lookup(I7,'sheet2'!A1:A77,VLOOKUP(J7,'sheet2'!A1:K1---no clue at this point))

    "M"=gender M/F
    I7=repetitions
    J7 which would be the age
    Because I don't understand how to then look up both the age cross-referenced with the repetitions I am at a loss.

    I would be more than willing to send someone the idea in the form of a spreadsheet so that "they" could look at it. Maybe the explanation isn't very clear.

    **this is like trying to speak German in Italian**
    Attached Files Attached Files
    Last edited by pepsijoe; 09-30-2009 at 08:26 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Close answers

    You can post the workbook here ...
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Close answers

    pepsijoe,

    Thanks for editing your thread title as requested
    Last edited by daddylonglegs; 09-30-2009 at 08:32 PM.

  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Close answers

    Hi,

    try this:

    you can do a MATCH on the age, but only if your age groups are displayed as a single number. To do that, you can enter the maximum age of the group in the cell above. For example in cell B2 on Male Tables put 21, C2 will be 26, D2 will be 31, and so on. For 62+ put a 99 or a really high number.
    Now MATCH can use this as a reference range for age.

    Next, name your pushup table, from A3:K77. Give it the range name "male_pushup"
    Next, name the first column of the table, from A3:A77 with the range name "male_pushup_reps"

    Then do the same thing for the female pushup table, i.e. enter the maximum age as a single number in row B and name the table and first column "female_pushup" and "female_pushup_reps"

    Now you can use this formula on sheet APFT in column K (my example is the formula for row 24. Paste it in row 24, above the "JOSEPH" example, then copy and paste to the rest of the column.

    Please Login or Register  to view this content.
    Apply the same principle to the other fitness tables. You can copy the formula and then just exchange the range names for the fitness table.

    Do you see how the formula works? Will this work for you?

    see attached

    BTW, you have two range names that link to other workbooks. These may no longer work in the attached file, since the links don't exist on my system.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-30-2009
    Location
    APG, MD
    MS-Off Ver
    Excel 2008
    Posts
    5

    Re: Problem with Hlookup, Vlookup, Index with table

    Is there an easier way to do this? The only reason I ask, is because the formula is not returning the proper number from the data table--but the number above it. Additionally, if the number of repetitions is above the amount listed a reply of !REF! is given back to me.

    I am trying to make this so that I understand it, even if it is the long way around.

    What if I were to split the "M" and "F" groups up, then identified the age by a numeric value...i.e., 17-21=1, 22-26=2 etc. Then--of course--I would have to do the same thing with the number of repetitions. I know this may seem more difficult, but I just don't understand how to work with the other formula.

    Thanks again for the help!

  6. #6
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Problem with Hlookup, Vlookup, Index with table

    Hi,

    the formula is not returning the proper number from the data table--but the number above it
    It does return the correct number in the example file attached in my previous post. If it does not on your workbook, then check that you have defined the ranges correctly.

    If you need to adjust the formula, here is an explanation how it works:

    =INDEX(male_pushup,MATCH(APFT!J27,male_pushup_reps,0),MATCH(APFT!G27,'Male Tables'!$B$2:$K$2,1)+2)

    The colored parts in the MATCH above mean:
    blue = the value in cell J27 on the APFT sheet
    green = the range A3:A77 on the "Male Tables" sheet
    so Match finds the number in J27 in the first column of the Pushup table and returns the number of the row where the value is found.
    If your male_pushup_reps does not start in A3, the result will seem to be wrong

    =INDEX(male_pushup,MATCH(APFT!J27,male_pushup_reps,0),MATCH(APFT!G27,'Male Tables'!$B$2:$K$2,1)+2)

    the blue bit above is the age in cell G27
    the MATCH formula finds the closest age that is smaller or equal to G27 in row 2 of the Male Tables sheet and returns the number of that column
    (If you don't want to show the values above that table, you can build another set of cells with the same entries anywhere else in the sheet and MATCH can go look there.) The only important thing is that MATCH will calculate the number of the column so that INDEX knows where to look.
    With the last parameter of the MATCH formula being 1, MATCH actually returns the column for the age group before the one we want. That (and the fact that the INDEX formula around it actually starts one column to the left) is the reason why I add a 2 to the result of the MATCH.

    So far, we've had two MATCH formulas return two numbers. If, for example we have a 42 year old male who does 63 pushups, the first MATCH will return 61 and the second match will return 5, we add 2 and end up with 7

    So now our formula looks like this

    =INDEX(male_pushup,61,7)

    This means, look in the table that is defined by the range name male_pushup, go to the 61st row of that table and return the value in the 7th column on that row.
    Now, for this to be the correct result, male_pushup needs to be defined correctly, i.e. from A3:K77 on the Male Tables sheet.

    Follow this in my previously attached example. It works.

    Additionally, if the number of repetitions is above the amount listed a reply of !REF! is given back to me.
    In my previously attached sheet it returns #N/A, not #REF, if the number of pushups is not found in the table, but that can easily be rectified. Change the last parameter of the first MATCH to be 1, not zero, then it will return the closest smaller number if the exact value is not found. That is also the approach you would need for your run tables, because you do not have every possible value in that table

    Mind you, the formula will return #REF! if there is no age or no number of repetitions in the current row, so the formula does not know what to look for in the first place. So if you have not entered the birth date or the number of repetitions, the formula will return #REF!, but as soon as you enter them it will return the correct number.

    Here is the revised formula again. Put it in cell K2 and copy down

    =IF(B2="M",INDEX(male_pushup,MATCH(APFT!J2,male_pushup_reps,1),MATCH(APFT!G2,'Male Tables'!$B$2:$K$2,1)+2),INDEX(female_pushup,MATCH(APFT!J2,female_pushup_reps,1),MATCH(APFT!G2,'Female Tables'!$B$2:$K$2,1)+2))

    And here is a version of the formula that does not use range names, but defines the cells directly. I find it more difficult to work with, but maybe it will be easier for you.

    again, this is for K2 and copy down

    =IF(B2="M",INDEX('Male Tables'!$A$3:$K$77,MATCH(APFT!J2,'Male Tables'!$A$3:$A$77,1),MATCH(APFT!G2,'Male Tables'!$B$2:$K$2,1)+2),INDEX('Female Tables'!$A$3:$K$50,MATCH(APFT!J2,'Female Tables'!$A$3:$A$50,1),MATCH(APFT!G2,'Female Tables'!$B$2:$K$2,1)+2))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1