Vlookup Using Two Criteria

[santeria]

I am not sure if Vlookup is the ideal way of doing this, but...

I am trying to set up a Vlookup to match on the basis of two criteria... A group ( 1,2,3,4,5), and a Week ( 1,2,3,4).
The Group Number and the week number will obviously be in different columns, but even if I have to change the Week designation to a letter, thats fine, so long as I can get a lookup for group and date.

I can do a vlookup for a staff member based on number, but the row will pull extra data as well ,related to the person, but the key search is by Group number and by Date week.

Any Suggestions or references appreciated, I can't seem to find anything applicable in the archives.


Ta

 

[Chitosunday]

For big data, I think it will be more efficient if you will use advance filter (click data filter advance filter) with a macro in the sheet change event. Everytime you change something in specific cell, it will active the advance filter . Too much formula will slow your computer.

 

[santeria]

Aladin's Formula Method works, I am satisfied of that.
In fact the filter method works great for disignating groups and weeks.

I have an idea for a modified Vlookup, if Aladin does not step in at this time, but I would prefer to use Aladin's Method, since I am impressed in how capable it is.

Ta

 

[just_jon]

Here is a respost of the relevant part of the File I am having grief over.
Help much appreciated.

AL Feb. Call Quality.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
33	GroupWeek		DATE	NAME					SKILLELEMENTSMISSED
34	11			77	21	2	1
35			2/16/2004	JOSEPHSWATTS					CorrectInformation
36	21	22	02/04/04	MichelleMills					0
37		22	02/04/04	MichelleMills					0
38		22	02/04/04	MichelleMills					0
39		22	02/04/04	MichelleMills					0
February 2-6

The file is producing the right number if unique references , 11 in this example, but it is not displaying the unique lines from the data tab.




Ta

In B36, change all references of A33 to A34 and N(A35) to N(B35). Enter Pos in A34

See if that helps.

 

[santeria]

Thanks, I'll try that, and see what Happens.


Ta

 

[santeria]

Beautiful Fix.

Except.... If I Place POS in A34, then a key formula Goes.

Also, that Number gives the Total I need for Other Elements of Calculation.

For the Demonstration Purposes, the figure is 11, the formulas in Date, name, and Skill Element mix suddenly bring up a #NA error.

The is a Beautiful Search device, but it just needs some tweaking I guess.

Any idea on those #NA errors?


Ta Muchly.

 

[just_jon]

In B36, change all references of A33 to A34 and N(A35) to N(B35). Enter Pos in A34

See if that helps.

Sorry; try In B36 change A33 to A35; change all references N(A35) to N(B35) ; enter Pos in A35; Copy B36 down as far as needed.

 

[santeria]

I must be worse off than I thought... I thought I saw another Reply in here before...

Anyways, with the Formulas, it seems that it displays a #NA in the last cell of the group. It displays the right number of Monitors, but Does not work right in the cell after the Last correctly displayed Monitor.

Any ideas ???

Ta

 

[santeria]

Thanks !!!!!!!!

Just_Jon, you are a genius !!!!!

Works Beautifully.

Now I have to figure how to take this from the Test File to a Live Page

But thats my Headache.....

I appreciate this Totally !!!!!!!


Ta


ray: ray:

 

[just_jon]

I must be worse off than I thought... I thought I saw another Reply in here before...

That was, then wasn't, me.

 

[santeria]

Is that a Zen Quote ?

I feel a Haiku coming on.

I Excel to Do My Job
But then It's Formulas Get quite Odd,
Some are there but then are not,
It really works, but ... maybe not



Ta

I must be worse off than I thought... I thought I saw another Reply in here before...

That was, then wasn't, me.