Vlookup Using Two Criteria

santeria · Feb 17, 2004

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

santeria · Feb 19, 2004

The Method 1 Fails at the Data Validation Point.

It's also not clear if this is for the Data fields, or the Place where the data is to be expressed, especially since the Method Looks incompatible to the Data areas.

MMm, I sure hope I have made a big mistake... this looks so promising.

Ta

santeria · Feb 19, 2004

Okay
Fixed that Data Validation thing.

After the B2 Enter details in Method 1, it fails to Make Sense.
Mainly because Column B has the Group numbers, and Column C has the Week Numbers.

How do I modify this ?

Ta

santeria · Feb 19, 2004

The Back-end where the Data is, seems moderately Confusing, anyway, I should get there.

However, the Front End draws on the Data Back end, and that looks like this pic below, and I do not know if the Formula in Column A works, and if it will also pull the data for the other columns

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

33

DATE

NAME

SKILLELEMENTSMISSED

34

8

35

2/9/2004

ANTOINETTEABROOMFIELD

36

37

38

39

40

41

February 2-6

The First Line of personal details is based on a formula draw to another sheet, the lines below that with REF errors are the ones I am trying to adapt for the current info.

Ta

santeria · Feb 19, 2004

Mmm, this seems to be making less sense as I go along.

I know the answer is there, the thing is what is the structure ?

Data Source Shows Group, week, Team Leader, Date Monitored, Monitoring score, Feedback give, and a few more columns ( which will be concatenated).

The Data Sheet where the Data Goes to just has Date, Name, Skille Elements missed.

The hope is that in cells C34 and D34, I can have Group then week respectively., and then have the data display in subsequent field from line 34 or line 35 down.

Thus far I don't seem to be connecting the dots.

santeria · Feb 19, 2004

Well, I have tried so many permutations, it really is a Puzzle.

The key thing is adapting the guide, but how ?

Mmmm, confused again.

Still trying, since the formulas appear to work, but nothing quite clicks to get the full answer

santeria · Feb 19, 2004

It appears that the formula

=IF((B3<>"")*(C3<>""),B3&CHAR(127)C3,"")

Is wrong.

There is a missing Ampersand.

santeria · Feb 19, 2004

Okay. Seems to be working now.

What I can't figure is why the right number of Monitors is working, but the Details for each person are not unique, thet are hiccuping, they are are repeating.

as Below.

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

36

37

38

39

February 2-6

Any help much appreciated.

Ta

santeria · Feb 19, 2004

santeria said:
Okay. Seems to be working now.

What I can't figure is why the right number of Monitors is working, but the Details for each person are not unique, thet are hiccuping, they are are repeating.

as Below.
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

Any help much appreciated.

For the purpose of the exercise , ignore line 35... it's a leftover from an alternative idea.

Ta