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**

You can post the workbook here ...

pepsijoe,

Thanks for editing your thread title as requested

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.

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))