Hi All
How do I include Four criteria cells to determine the row and one criteria
cell to determine the COLUMN to find a specific cell value? e.g

Sheet One (the criteria & the formula)
A B C D E F
G
R10 Jan Feb
Mar
R11
R12
R13
R14
R15 18-24
R16
R17 SYD 45 XXX XXX

Criteria:
$A17 = SYD
$B17 = 45
$C$15 = 18-24
E$10 = Jan

Sheet Two (the data to search through and match the criteria too)
A B C D E F
R3 Place Size Age Jan Feb Mar
R4 SYD 60 18-24 100 80 60
R5 SYD 45 18-24 50 75 55
R6 SYD 60 25-54 101 81 61
R7 SYD 45 25-54 51 76 55
ETC

The formula needs to go to Sheet Two (A4:AE360) and match to a row that
includes:
"45" in column A
"SYD" in Column B,
"18-24" in Column C

and then go to the Jan Column and report the intersecting price = 50
Any help would be greatly appreciated.
--
Thanks
BeSmart

Hi,

Here's a triple index match but note that you may have a problem with SYD.
SYD is a statistical function in Excel and because of that the only way I
could get the formula to work was to enter 'SYD in my test data. Change the
ranges to suit your need then array enter the formula. The 3 lookup values
are in D1, E1 & F1.


=INDEX(D4:D20,MATCH(1,(A4:A20=D1)*(B4:B20=E1)*(C4: C20=F1),0))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"BeSmart" wrote:

Hi All
How do I include Four criteria cells to determine the row and one criteria
cell to determine the COLUMN to find a specific cell value? e.g

Sheet One (the criteria & the formula)
A B C D E F
G
R10 Jan Feb
Mar
R11
R12
R13
R14
R15 18-24
R16
R17 SYD 45 XXX XXX

Criteria:
$A17 = SYD
$B17 = 45
$C$15 = 18-24
E$10 = Jan

Sheet Two (the data to search through and match the criteria too)
A B C D E F
R3 Place Size Age Jan Feb Mar
R4 SYD 60 18-24 100 80 60
R5 SYD 45 18-24 50 75 55
R6 SYD 60 25-54 101 81 61
R7 SYD 45 25-54 51 76 55
ETC

The formula needs to go to Sheet Two (A4:AE360) and match to a row that
includes:
"45" in column A
"SYD" in Column B,
"18-24" in Column C

and then go to the Jan Column and report the intersecting price = 50
Any help would be greatly appreciated.
--
Thanks
BeSmart

Thanks to you both Mike & Teylyn.

I've used Teylyn's formula - mainly because that's the one I understood the
quickest - and it's working PERFECTLY!!!!

Mike - thanks heaps for the advice on using SYD - I've replaced all
occurrences with another coding.

--
As usual - much appreciation
BeSmart


"Mike H" wrote:

Hi,

Here's a triple index match but note that you may have a problem with SYD.
SYD is a statistical function in Excel and because of that the only way I
could get the formula to work was to enter 'SYD in my test data. Change the
ranges to suit your need then array enter the formula. The 3 lookup values
are in D1, E1 & F1.


=INDEX(D4:D20,MATCH(1,(A4:A20=D1)*(B4:B20=E1)*(C4: C20=F1),0))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"BeSmart" wrote:

Hi All
How do I include Four criteria cells to determine the row and one criteria
cell to determine the COLUMN to find a specific cell value? e.g

Sheet One (the criteria & the formula)
A B C D E F
G
R10 Jan Feb
Mar
R11
R12
R13
R14
R15 18-24
R16
R17 SYD 45 XXX XXX

Criteria:
$A17 = SYD
$B17 = 45
$C$15 = 18-24
E$10 = Jan

Sheet Two (the data to search through and match the criteria too)
A B C D E F
R3 Place Size Age Jan Feb Mar
R4 SYD 60 18-24 100 80 60
R5 SYD 45 18-24 50 75 55
R6 SYD 60 25-54 101 81 61
R7 SYD 45 25-54 51 76 55
ETC

The formula needs to go to Sheet Two (A4:AE360) and match to a row that
includes:
"45" in column A
"SYD" in Column B,
"18-24" in Column C

and then go to the Jan Column and report the intersecting price = 50
Any help would be greatly appreciated.
--
Thanks
BeSmart