VLOOKUP - Multiple Criteria

[gregula82]

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.

 

[OneTwo]

I'm not using sumifs and averageifs with the lookup formula.
I tough that the solution was by using the lookup formula and I tried a few things. But using just sumifs or averageifs I get what I need.

 

[Aladin Akyurek]

I'm not using sumifs and averageifs with the lookup formula.
I tough that the solution was by using the lookup formula and I tried a few things. But using just sumifs or averageifs I get what I need.

It is better to post a sample of representative data, so you can avoid getting formulas, supposed to work on non-existing data.

 

[ramgouda]

I was having a similar problem with searching on multiple criteria and the solutions offered here in this thread very insightful, easy to follow. Hat's off to T.Valko and Aladin. Thanks again for your great help.

 

[Aladin Akyurek]

I was having a similar problem with searching on multiple criteria and the solutions offered here in this thread very insightful, easy to follow. Hat's off to T.Valko and Aladin. Thanks again for your great help.

It's pleasing to know that. Thanks for the kind feedback.

 

[chrisp2005]

All, I've read through this thread and tried many of the formulas, but unfortunately cannot make my lookup work. I'm attempting to pull a number of hours for a specific employee and charge code for a month from one sheet into another, and can't seem to make the formulas work. The sheets are inserted below.

Charge Number	Employee Name: Last, First	Sep-13	Oct-13	Nov-13	Dec-13	Jan-14	Feb-14	Mar-14
AFNC12345	Dole, Bob	152	152	152	176	161	64.5	96.5
AFNC12345	Smith, John	152	156	146	134	146	10.5	6
AFNC12345	Doe, Jane	123.5	156	134.5	113	59.5	88	117
AFNC12345	Dockins, Larry	149.5	141	136.5	169	127.5	84.5	132
AFNC12345	Bennet, Tony	144	152	147	176	62	140.5	183
AFNC12345	Matthias, Carol	108	160	152	200	160	160	200
BGOD23456	Dole, Bob	31
BGOD23456	Smith, John	148	134	136	172.5	166	136	193.5
BGOD23456	Doe, Jane	113	77.5	155.5	36.5

<tbody>
</tbody>

The upper sheet has all of the data - trying to look up a number in that sheet (hours) based on the Charge Number, Name, and Date in the next sheet. The formula below was one I was trying to use, but couldn't seem to make it work.

=LOOKUP(2,(('Labor pivot'!$A:$A=Labor!$E9)*('Labor pivot'!$B:$B=Labor!$F9)*('Labor pivot'!$H$2=Labor!T$2)),$C$2:$C$1000)

Role	Charge Number	Name	Sep-13	Oct-13	Nov-13	Dec-13	Jan-14	Feb-14	Mar-14
Program Management	AFNC12345	Smith, John
Program Management	AFNC12345	Doe, Jane
Program Management	AFNC12345	Dockins, Larry
Development	BGOD23456	Dole, Bob
Development	BGOD23456	Smith, John

<tbody>
</tbody>

Any help would be great, thank you!

 

[Excelaron]

Thanks for the explanation, I always wondered what the 1 function was. I played around the this function using the F9 key and now I see.

 

[Aladin Akyurek]

Let's Sheet1 house the data (the first exhibit) and Sheet2 the second exhibit.

Insert a new column before the current first column.

In A1 enter: Idx (from indexing).

A2, control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(Sheet1!$A$2:$A$10=$C2,Sheet1!$B$2:$B$10=$D2,
  ROW(Sheet1!$A$2:$A$10)-ROW(Sheet1!$A$2)+1),ROWS($A$2:A2)),"")

E2, just enter, copy across, and down:

=IF($A2="","",INDEX(Sheet1!$C$2:$I$10,$A2,MATCH(E$1,Sheet1!$C$1:$I$1,0)))

 

[ramgouda]

It's pleasing to know that. Thanks for the kind feedback.

Hi I am trying to use index/match function as explained in this thread but quite not get it working for my situation.

Problem: I am required to produce a weekly report to check the variances against the working day 1 forecast with weekly actuals.
Sheet 1: Working Day 1 Forecast:
On day 1 of every month, I receive a report having the following information
Month, Programme ID, Project ID, Resource Name,Task Name, planned hours, planned cost.

Sheet 2: Weekly Actuals:
Every Friday, I receive the weekly actuals with the following information.

Month, Programme ID, Project ID, Resource Name, Task Name, Actual Hours, Actual Cost.

Sheet 3: Weekly MI Report:
The report I produce weekly have the following columns

Month, Programme ID, Project ID, Resource Name, Task Name, WD1 Planned Hours, WD1 Planned cost, Actual hours, Actual Cost, var in hours, var in cost.

For this report, The WD1 planned hours and WD planned cost in Sheet 3 need to be populated weekly basis by comparing the first five columns in Sheet 1 with the first five columns in Sheet 3. The rest of the data I just populate from Sheet 2 except for Variances which are calculated fields.

The no. of records in Sheet 3 are always greater than the no.of records in Sheet 1 because resources who are not included in the day1 forecast will be booking time to project and their actuals will be coming through. I want to populate 0.00 against these new resources planned cost and planned hours.

Also, the volume of rows at the beginning of the year is 6000 and it keeps increasing every week and by end of the year it can reach about 10000.

Any help is greatly appreciated.

 

[ramgouda]

Hi I am trying to use index/match function as explained in this thread but quite not get it working for my situation.

Problem: I am required to produce a weekly report to check the variances against the working day 1 forecast with weekly actuals.
Sheet 1: Working Day 1 Forecast:
On day 1 of every month, I receive a report having the following information
Month, Programme ID, Project ID, Resource Name,Task Name, planned hours, planned cost.

Sheet 2: Weekly Actuals:
Every Friday, I receive the weekly actuals with the following information.

Month, Programme ID, Project ID, Resource Name, Task Name, Actual Hours, Actual Cost.

Sheet 3: Weekly MI Report:
The report I produce weekly have the following columns

Month, Programme ID, Project ID, Resource Name, Task Name, WD1 Planned Hours, WD1 Planned cost, Actual hours, Actual Cost, var in hours, var in cost.

For this report, The WD1 planned hours and WD planned cost in Sheet 3 need to be populated weekly basis by comparing the first five columns in Sheet 1 with the first five columns in Sheet 3. The rest of the data I just populate from Sheet 2 except for Variances which are calculated fields.

The no. of records in Sheet 3 are always greater than the no.of records in Sheet 1 because resources who are not included in the day1 forecast will be booking time to project and their actuals will be coming through. I want to populate 0.00 against these new resources planned cost and planned hours.

Also, the volume of rows at the beginning of the year is 6000 and it keeps increasing every week and by end of the year it can reach about 10000.

Any help is greatly appreciated.

 

[Aladin Akyurek]

@ramgouda

To bump up, you don't need to repeat thec ontents of your post. Just "bump" would suffice. That said...

You seem to ask too much. Try to cut your question in manageable subquestions, supported with sample data and the desired output.