Multiple criteria search

[BeSmart]

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.

 

[teylyn]

Hi, the following formula works with Sheet2 having the labels "Jan"
"Feb", etc in row 1. Adjust ranges to suit.

=INDEX(Sheet2!A1:F5,MATCH(Sheet1!A17&Sheet1!B17&Sheet1!C15,INDEX(Sheet2!A1:A5&Sheet2!B1:B5&Sheet2!C1:C5,0),0),MATCH(E10,Sheet2!A1:F1,0))

Index being non-volatile, the formula should perform reasonably fas
with larger ranges, too.

regards

teylyn

'The Code Cage - Microsoft Office Help - Microsoft Office Discussion
(http://www.thecodecage.com)

 

[teylyn]

Here a version with the labels in row 3 and the data starting in row 4

=INDEX(Sheet2!A3:F7,MATCH(Sheet1!A17&Sheet1!B17&Sheet1!C15,INDEX(Sheet2!A3:A7&Sheet2!B3:B7&Sheet2!C3:C7,0),0),MATCH(E10,Sheet2!A3:F3,0))


Hi, the following formula works with Sheet2 having the labels "Jan"
"Feb", etc in row 1. Adjust ranges to suit.

=INDEX(Sheet2!A1:F5,MATCH(Sheet1!A17&Sheet1!B17&Sheet1!C15,INDEX(Sheet2!A1:A5&Sheet2!B1:B5&Sheet2!C1:C5,0),0),MATCH(E10,Sheet2!A1:F1,0))

Index being non-volatile, the formula should perform reasonably fas with larger ranges, too.

regards

teylyn

'The Code Cage - Microsoft Office Help - Microsoft Office Discussion

(http://www.thecodecage.com)

 

[Mike H]

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(D420,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]

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.

 

[teylyn]

Thanks for the feedback, BeSmart. Mike had a very valid point.

If you ever feel the need to upload a file to illustrate your question
come to 'The Code Cage - Microsoft Office Help - Microsoft Offic
Discussion' (http://www.thecodecage.com)

regards

teylyn


Thanks to you both Mike & Teylyn.