Cross Value Across Two Sheets

[YourChaos]

I've been trying use excel to figure out amounts reserve Soldiers would be paid for working a certian number of days. I need to cross reference rank and time in service for each Soldier. I decided that it would be faster to use excel and use a cross reference formula to figure it out. I've tried VLOOKUP, HLOOKUP, and even OFFSET with MATCH, but none of them seem to want to work for me. Either end up with #REF!, #N/A, or sometimes I'll get a value back, but it's the wrong one. I have a copy of the spredsheet I can send if that makes it easier.
On the first sheet I have RANK and TIME IN SERVICE of the Soldier. I'm trying to get a value to return from the second sheet to populate a new cell.
On the second sheet, I have the TIME IN SERVICE across the top, and the RANKs on the left side column.
Examples of formulas used:

Where on Sheet1 B2 is RANK, D2 is TIME IN SERVICE, and on Sheet2, A1:A28 is RANK, B1:W1 is TIME IN SERVICE.

=VLOOKUP('Sheet1'!B2,'Sheet2'!A2:A28,MATCH('Sheet1'!D2,'Sheet2'!B1:W1,0))

This comes back with #N/A!

=HLOOKUP('Sheet1'!D2,'Sheet2'!B1:W1,MATCH('Sheet1'!B2,'Sheet2'!A2:A28,0))

This comes back with #REF!

Thanks for your help.

 

[SamTYler]

HLookup needs a row reference.

Row(VLookUp(B2,'Sheet2'!A2:A28,1)) Returns the Rank Row

Substitute for the Row reference in the next

HLOOKUP(D2,'Sheet2'!B1:W28,Row(VLookUp(B2,'Sheet2'!A2:A28,1)))


Put the formula in a column, Row 2, on sheet1 and copy down.

You could use the Column Function on HLookup and put that into the VLookUp.

 

[YourChaos]

Finally figured it out. This is what I came up with:

=IF(B2="","",(INDEX(Sheet2!B2:W28,MATCH(Sheet1!B2,Sheet2!A2:A28,0),MATCH(IF(E2>4,FLOOR(Sheet1!E2,2),E2),Sheet2!B1:W1,0))))

This allowed me to cross reference across them and then allowed me to rounddown to the nearest even number if the time in service was over 4 years.