Vlookup Using Two Criteria

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
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

(y)
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Ally Bally

New Member
Joined
Oct 18, 2003
Messages
15
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

Legend
Joined
Sep 3, 2002
Messages
10,473
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

Well-known Member
Joined
Oct 7, 2003
Messages
1,844

ADVERTISEMENT

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

(y)
 

Ally Bally

New Member
Joined
Oct 18, 2003
Messages
15
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

Ally Bally said:
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

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
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

(y)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
santeria said:
...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

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
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

(y)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,101
Messages
5,768,101
Members
425,453
Latest member
bince

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top