Nested Limitations

[_Bigred]

I have a excel worksheet that I want to do the following:

If A1 has OFC and B1 has 1992 then C1 returns a value of 20.33

However I'm trying to create the formula that would have a 82 different
outcomes, because a
officer with a 1965 seniority date makes $24.50
sgt with a 1965 seniority date makes $26.75
officer with a 1966 seniority date make $24.00
sgt with a 1966 seniority date makes $26.40
etc...

I ran across the following link that deals with getting around the nested
limitations, but I don't know what they are talking about when they say
create a named formula?

what is the best way to set this type of data up, so the user can indicate
they are a officer or sgt in 1 cell,
and input their seniority year in another cell and have it pull the wage off
a list that is 3 colums filled with
ColA ColB ColC
Row1 OFC 1965 24.50
Row2 SGT 1965 26.75
Row3 OFC 1966 24.00
Row4 SGT 1966 $26.40
these type of data would continue for officers and sgt from 1965 thru 2005.

I really don't know VB code, and don't seem to be able to make a LOOKUP or
VLOOKUP work with
my data.

How should I approach this.
TIA,
_Bigred

 

[Max]

One way ..

Assuming source data is within A1:C100,

Inputs for rank & seniority will be made:

In E1: SGT (say)
In E2: 1965 (say)

Put in the formula bar for E3,
then array-enter by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):

=IF(OR(TRIM(E1)="",E2=""),"",
INDEX(C1:C100,MATCH(1,(A1:A100=TRIM(E1))*(B1:B100=E2),0)))

E3 will return the wages from col C corresponding to the inputs in E1:E2

 

[Biff]

Hi!

Try this:

This table is on Sheet2 A1:C4:

ColA ColB ColC
Row1 OFC 1965 24.50
Row2 SGT 1965 26.75
Row3 OFC 1966 24.00
Row4 SGT 1966 $26.40

On Sheet1:

A1 = OFC
B1 = 1966
C1 = formula:

=SUMPRODUCT(--(Sheet2!A1:A4=A1),--(Sheet2!B1:B4=B1),Sheet2!C1:C4)

Biff

 

[George]

And here is even a third approach,
isn't Excel wonderful to have so many options available

Move Column C to Column D
C1 = A1&B1 and copy this down to all the rest

Then lets assume;
E1 = Work Position (OFF or SGT)
and E2 = Year

Then E3 = VLOOKUP(E1&E2,$C$1:$D$82,2,FALSE)


George