PDA

View Full Version : Solved: VLOOKUP on multiple criteria



marshybid
07-29-2008, 06:41 AM
Afternoon all,

Can anyone help me with how to write the VLOOKUP formula to be based on multiple criteria;

Example - Sheet 1, Column A contains a persons name, Column B contains an ID number, Column C contains a status

Sheet 2 Column A contains a persons name, Column B contains an ID number

ID numbers are not unique so for sheet 2 I would like to lookup the persons name (column A in sheet 1), if I find an exact match then see if the ID number in Column B also matches, if yes then insert sheet 1 Column C value into Column C in sheet 2

Help please :dunno

Marshybid

Bob Phillips
07-29-2008, 06:48 AM
=INDEX(Sheet1!C:C,MATCH(1,(Sheet1!A1:A200=A2)*(Sheet1!B1:B200=B2),0))

as an array formula

marshybid
07-29-2008, 06:53 AM
=INDEX(Sheet1!C:C,MATCH(1,(Sheet1!A1:A200=A2)*(Sheet1!B1:B200=B2),0))

as an array formula

Thanks xld, as ever your knowledge of formulas never ceases to amaze :rotlaugh:

Marking as solved

Marshybid :hi:

Bob Phillips
07-29-2008, 08:20 AM
Formulae are Excel's shining glory IMO, they underpin everything.