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

 

[Aladin Akyurek]

http://216.92.17.166/board2/viewtopic.php?t=77416&highlight=multikey

 

[Ally Bally]

Try concatenating the group number and week number creating a unique ID for both the source and target data. Use this to find a match.

 

[just_jon]

VLOOKUP does not like concatenation but INDEX/MATCH will allow. With groups in A, weeks in B and data to retrieve in C --

=INDEX(C:C,MATCH(grouphere&CHAR(127)&weekhere,A:A&CHAR(127)&B:B,0),0)

Must be entered with Control+Shift+Enter.

 

[santeria]

Okay,
Index Match looks the best way.

The Data to retrieve is on a separate tab.

What I would like to do is have someone put in a Cell above the display line a Group Number, then a week number, and then the cells below the weekly summary will display the information.

In theory this could be by up to 86 people in four different weekly groups.

Can I combine a Vlookup to get the Row data, or will I have to stick with Index match?

Hopefully this is not too unclear.

The essential things is that if I can pull the rows then the rest should follow.

Ta

 

[Ally Bally]

Hi,
Maybe an incorrect way but I use concatenation and Vlookup a lot and it works for me - worth a try possibly?

First: Concatenate the target data using a spare column eg in C2 put =A2&B2. Copy the formula down the full range of the data.

Second: On another worksheet use two adjacent cells, say A2,B2 & C2 one for group and one for week, use the formula as above to concatenate the result into C2l. In D2 use the formula: Vlookup(C2, the range target data(Sheet2 C2:Z356,the number of the column to the right that contains the data you wish to see, false). Remeber to include the concatenated target data as the first column of the range. Should work in a messy but effective way.
Ally

 

[Aladin Akyurek]

Hi,
Maybe an incorrect way but I use concatenation and Vlookup a lot and it works for me - worth a try possibly?

First: Concatenate the target data using a spare column eg in C2 put =A2&B2. Copy the formula down the full range of the data.

Second: On another worksheet use two adjacent cells, say A2,B2 & C2 one for group and one for week, use the formula as above to concatenate the result into C2l. In D2 use the formula: Vlookup(C2, the range target data(Sheet2 C2:Z356,the number of the column to the right that contains the data you wish to see, false). Remeber to include the concatenated target data as the first column of the range. Should work in a messy but effective way...

Have a look at the link I quoted. If you do the concatenation in a column in front of the data, you can use a formula with VLOOKUP. If you do the concatenation in a column after the last column of data, you need INDEX/MATCH.

BTW, the concatenation (without a separator) you propose is extremely risky here. See the link for a surefire way of doing it.

 

[santeria]

Aladin,

I know that it's best to follow your way...
So... The Link you gave has a link within a link.
Is the first link the key, or the link within a link.

Anyways, the setup I have has the core data in the Back tab.
Basically Irrelevant for the purpose of the presentation.

The formula I am trying to set up will have it so that the front tab will hopefully display fields for the groups.
The linking by a group+week style of code is so that the data done by group and week will display in a summary field unlikely to exceed 20 rows, but it might.

I guess the question in addition might be: for each line on the summary tab, do I need to have a formula that calls the next row down in the back tab data? or do I have to have one principle formula at line 1 in the front tab, and then something else on subsequent tabs that will pick up the next available row for the group plus week value...

I hope this is clear.

Ta

 

[Aladin Akyurek]

...The Link you gave has a link within a link.

Both illustrates the same concept. The example given looks similar to your situation, but now I'm not that sure while trying to understand what you say below.

...Anyways, the setup I have has the core data in the Back tab.
Basically Irrelevant for the purpose of the presentation.

The formula I am trying to set up will have it so that the front tab will hopefully display fields for the groups.

The set up doesn't require the data along with concatenated column to be in the same sheet as the formulas that do the retrieval.

The linking by a group+week style of code is so that the data done by group and week will display in a summary field unlikely to exceed 20 rows, but it might.

I guess the question in addition might be: for each line on the summary tab, do I need to have a formula that calls the next row down in the back tab data? or do I have to have one principle formula at line 1 in the front tab, and then something else on subsequent tabs that will pick up the next available row for the group plus week value...

I'm not sure about how to read the foregoing. If concatenation column does not house any duplicates, the retrieval formulas should work just fine.

 

[santeria]

I'm not sure how to phrase the statement/questions etc...

I know the above formulas cover what I need to do.
Thats the main point.

The Data Source has multiple rows. No duplicates.
The common factor is that certain lines have come from the same group, and each group will have a reporting period over a weekly time slot.

These rows are supposed to be displayed in the front of the Summary Sheet.
Ideally, if someone keys in, for example, something like "1a" in a cell at around row 15, column C ( without the " ) then the rows on tab one will pull data from rows in the data area that match Group 1, and week a.

It's easy enough to do this for names if they are sequential, but since the info may not necessarily be sequential, then thats a problem.

I know it can be done, I just have to figure that out, or at least find some formula that is adaptable to the issue.


Ta