VLOOKUP - Multiple Criteria

gregula82 · Mar 2, 2008

Hi Everyone,

I was wondering whether anyone knew of a way of performing a VLOOKUP function which has multiple criteria. e.g. the lookup value would have 3 separate criteria and then you put the table array in and the column index.

Any ideas???

Thanks.

Greg.

ramgouda · Jun 22, 2014

@Aladin

Apologies for the repeat.

I will try to simplify my question, I am trying to populate WD1 planned hours and WD1 planned cost on sheet 2 from sheet 1

sample data on Wd1 Sheet:

Month	Programme ID	Project ID	Resource Name	Task Name	WD1 PH	WD1 PC
1	201	P268	Name 1	T1	14	112
1	202	P298	Name 2	T2	14	112
1	203	P299	Name 3	T3	14	112
2	201	P268	Name 4	T1	28	224
2	202	P269	Name 1	T2	35	280
3	201	P269	Name 2	T3	35	280
3	202	P268	Name 3	T1	42	336
4	201	P299	Name 4	T4	14	112
4	202	P268	Name 5	T1	7	56
5	201	P298	Name 6	T3	7	56
5	203	P268	Name 1	T2	56	448
6	203	P267	Name 2	T2	49	392
6	204	P268	Name4	T5	49	392

Sample Data on Sheet2

Month	Programme ID	Project ID	Resource Name	Task Name	PH	PC	WD1 PH	WD1 PC
1	201	P268	Name 1	T1	14	112	14	112
1	202	P298	Name 2	T2	14	112	14	112
1	203	P299	Name 3	T3	14	112	14	112
2	201	P268	Name 4	T1	28	224	28	224
2	202	P269	Name 1	T2	35	280	35	280
3	201	P269	Name 2	T3	35	280	35	280
3	202	P268	Name 3	T1	42	336	42	336
4	201	P299	Name 4	T4	14	112	14	112
4	202	P268	Name 5	T1	7	56	7	56
5	201	P298	Name 6	T3	7	56	7	56
5	203	P268	Name 1	T2	56	448	56	448
6	203	P267	Name 2	T2	49	392	49	392
6	204	P268	Name 4	T5	49	392	49	392
6	229	P290	Name 7	T5	14	112	#N/A	#N/A
6	202	P298	Name 1	T4	7	56	#N/A	#N/A

I am looking for a formula to populate column H and I with values from sheet 1 columns F and G when a match is found. if not populate #N/A.

Aladin Akyurek · Jun 22, 2014

Care to specify "a match of what from Sheet2 against what in Wd1"?

ramgouda · Jun 22, 2014

Hi Aladain,

Hi Aladin,

The value from the combination of the following five columns should match.

Month

Programme ID

Project ID

Resource Name

Task Name

In other words,
If [(Sheet1.A2=Sheet2.A2)And((Sheet1.B2=Sheet2.B2)And(Sheet1.C2=Sheet2.C2)And(Sheet1.D2=Sheet2.D2)And(Sheet1.E2=Sheet2.E2)=True]
then sheet2.H2 should have the value from Sheet1.F1 and sheet2.I2 should have the value from Sheet1.G2.

Hope I am making sense. Apologies for any confusion and thanks for your time in looking into it. Much appreciated.

Aladin Akyurek · Jun 22, 2014

ramgouda said:
Hi Aladain,

Hi Aladin,

The value from the combination of the following five columns should match.

Month
Programme ID
Project ID
Resource Name
Task Name

<TBODY>
</TBODY>

In other words,
If [(Sheet1.A2=Sheet2.A2)And((Sheet1.B2=Sheet2.B2)And(Sheet1.C2=Sheet2.C2)And(Sheet1.D2=Sheet2.D2)And(Sheet1.E2=Sheet2.E2)=True]
then sheet2.H2 should have the value from Sheet1.F1 and sheet2.I2 should have the value from Sheet1.G2.

Hope I am making sense. Apologies for any confusion and thanks for your time in looking into it. Much appreciated.

Wd1, H2, copied down:

=A2&"|"&B2&"|"&C2&"|"&D2&"|"&E2

Sheet2, H2, copied across, and down:

=INDEX(Wd1!F:F,MATCH(A$2&"|"&$B2&"|"&$C2&"|"&$D2&"|"&$E2,Wd1!$H:$H,0))

markmzz · Jun 22, 2014

Another way:

Layout

Month	Programme ID	Project ID	Resource Name	Task Name	PH	PC	WD1 PH	WD1 PC
1	201	P268	Name 1	T1	14	112	14	112
1	202	P298	Name 2	T2	14	112		112
1	203	P299	Name 3	T3	14	112	14	112
2	201	P268	Name 4	T1	28	224	28	224
2	202	P269	Name 1	T2	35	280	35	280
3	201	P269	Name 2	T3	35	280	35	280
3	202	P268	Name 3	T1	42	336	42	336
4	201	P299	Name 4	T4	14	112	14	112
4	202	P268	Name 5	T1	7	56	7	56
5	201	P298	Name 6	T3	7	56	7	56
5	203	P268	Name 1	T2	56	448	56	448
6	203	P267	Name 2	T2	49	392	49	392
6	204	P268	Name 4	T5	49	392	#N/D	#N/D
6	229	P290	Name 7	T5	14	112	#N/D	#N/D
6	202	P298	Name 1	T4	7	56	#N/D	#N/D
******	*************	*********	***************	**********	****	****	********	*******

Formula

Code:

In H2 - use Ctrl+Shift+Enter to enter the formula

=VLOOKUP($E2,IF('Wd1'!$A$2:$A$14=$A2,IF('Wd1'!$B$2:$B$14=$B2,IF('Wd1'!$C$2:$C$14=$C2,IF('Wd1'!$D$2:$D$14=$D2,'Wd1'!$E$2:$G$14)))),MATCH(H$1,'Wd1'!$E$1:$G$1,0),0)

And copy to the right and down.

Markmzz

ramgouda · Jun 22, 2014

Aladin Akyurek said:
Wd1, H2, copied down:

=A2&"|"&B2&"|"&C2&"|"&D2&"|"&E2

Sheet2, H2, copied across, and down:

=INDEX(Wd1!F:F,MATCH(A$2&"|"&$B2&"|"&$C2&"|"&$D2&"|"&$E2,Wd1!$H:$H,0))

Thanks a bunch Aladin. Great help.

Aladin Akyurek · Jun 22, 2014

ramgouda said:
Thanks a bunch Aladin. Great help.

You are welcome. Thanks for providing feedback.

ScottVP · Sep 9, 2014

Not sure if this is the same or if I need to post this in another thread. This is really killing me.

I have a spreadsheet that I use VLookup to find some data. The name for the data can be STT1RM1 or it can be STT1TRM2 and the column (depending on which one exists) that I will return is column 4.

Below is my formula. Please help me understand what I am missing here.

=IF(ISNA(VLOOKUP("STT1RM1",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,FALSE)),“”,VLOOKUP("STT1RM2",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,FALSE))

Thank you! Great forum!

Aladin Akyurek · Sep 9, 2014

ScottVP said:
Not sure if this is the same or if I need to post this in another thread. This is really killing me.

I have a spreadsheet that I use VLookup to find some data. The name for the data can be STT1RM1 or it can be STT1TRM2 and the column (depending on which one exists) that I will return is column 4.

Below is my formula. Please help me understand what I am missing here.

=IF(ISNA(VLOOKUP("STT1RM1",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,FALSE)),“”,VLOOKUP("STT1RM2",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,FALSE))

Thank you! Great forum!

If VLOOKUP is expected to return a text value when successful...

Rich (BB code):

=IFERROR(LOOKUP(REPT("z",255),
  CHOOSE({1,2},VLOOKUP("STT1RM2",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0),
   VLOOKUP("STT1RM1",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0))),"")

If VLOOKUP is expected to return a number when successful...

Rich (BB code):

=IFERROR(LOOKUP(9.99999999999999E+307,
  CHOOSE({1,2},VLOOKUP("STT1RM2",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0),
   VLOOKUP("STT1RM1",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0))),"")

Hope this helps.

markmzz · Sep 10, 2014

ScottVP said:
Not sure if this is the same or if I need to post this in another thread. This is really killing me.

I have a spreadsheet that I use VLookup to find some data. The name for the data can be STT1RM1 or it can be STT1TRM2 and the column (depending on which one exists) that I will return is column 4.

Below is my formula. Please help me understand what I am missing here.

=IF(ISNA(VLOOKUP("STT1RM1",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,FALSE)),“”,VLOOKUP("STT1RM2",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,FALSE))

Thank you! Great forum!

Hi ScottVP,

If I understand correctly what you want, maybe this can helps:

Code:

=IFERROR(VLOOKUP("STT1RM1",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0),
IFERROR(VLOOKUP("STT1RM2",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0),""))

Markmzz

VLOOKUP - Multiple Criteria

gregula82

New Member

ramgouda

New Member

Excel Facts

Aladin Akyurek

MrExcel MVP

ramgouda

New Member

Aladin Akyurek

MrExcel MVP

markmzz

MrExcel MVP

ramgouda

New Member

Aladin Akyurek

MrExcel MVP

ScottVP

New Member

Aladin Akyurek

MrExcel MVP

markmzz

MrExcel MVP

Similar threads

Forum statistics

Share this page

VLOOKUP - Multiple Criteria

New Member

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock