Using MATCH on 2 Criteria

[dvent]

Hi All,

I have a formula:

=MATCH(P3,FTEs!A:A,0)

P3 contains the word "HELLO". Cell A2 on the FTEs sheet contains the word "HELLO". The result in my cell containing the formula is 2.

If I want to add another criteria to the match, i.e. find the cell where column A contains "HELLO" and column B contains "WORLD", how would I do this?

Thanks
dvent

[ratcat]

G'day dvent,

If I understand right try this

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Cheers

RC

[NBVC]

Try:

=MATCH(1,INDEX((FTEs!A:A=P3)*(FTEs!B:B=Q3),0),0)

Where Q3 contains item ("WORLD") to match to column B

Although, you should use defined column size...as this formula make take longer to process a million rows.

You can also produce a column that concatenates columns A & B, then apply

=MATCH(P3&Q3,FTEs!C:C,0)

where column C contains formula, =A1&B1 copied down in the FTE sheet.

[dvent]

thanks - how do i mark this solved?

[NBVC]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save