Too many variables?

[Guest]

I am trying to create a formula that looks at two variables on Sheet 1 and
returns a value from one of two columns on Sheet 3.

Essentially, what I'd like it to do is:

If B27>7 and B28<25, then return the value from sheet 3, C4 in C28 (first
sheet)
B28 is 25 to 29, then enter value from sheet 3, C5 in C28
B28 is 30 to 34, then enter value from sheet 3, C6 in C28
B28 is 35 to 39, then enter value from sheet 3, C7 in C28

etc. through B28>65, then enter value from sheet 3, C13 in C28,
OR
If B27<7 and B28<25, then return the value from sheet 3, B4 in C28 (first
sheet)
B28 is 25 to 29, then enter value from sheet 3, B5 in C28
B28 is 30 to 34, then enter the value from sheet 3, B6 in
C28
B28 is 35 to 39, then enter the value from sheet 3, B7 in
C28
etc. through B28>65, then enter value from sheet 3, B13 in C28

Is it possible to write one formula that looks at the first variable in B27
(< or > 7 – this designates gender), then the number in B28 (age) and returns
a rate (values on sheet 3) bases upon those two?

Thanks!!

 

[Guest]

You can use INDIRECT and VLOOKUP, but you need to create a new list for that.
In some place of the workbook make a list like:
1 4
25 5
30 6
35 7
....
The first column is the minimun of the range of ages, the second the row
that will be referenced if the age is in range (from 25 to 29, cell X6).
For the formula, let's suppose that this range is in A2:B10
=IF(B27<7,INDIRECT("Sheet3!B" & VLOOKUP(B28,A2:B10,2)),INDIRECT("Sheet3!C" &
VLOOKUP(B28,A2:B10,2)))
You can change the references to match your case, I would recommend using
names for some of the common ranges.

Hope this helps,
Miguel.

 

[Guest]

I didn't know about "INDIRECT," worked like a charm! Thank you!!