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.

 

[Aladin Akyurek]

Thank you for your insight. Have been working with this all day and it works great in the example I provided. Turns out I provided an oversimplified example and that breaks the solution all to pieces. The "huge honkin" budget sheet is actuall of the form

Jun	Jun	Jun	Jul	Jul	Jul	Q1	Q1	Q1	Q2	Q2	Q2
Actual	Budget	Commit	Actual	Budget	Commit	Actual	Budget	Commit	Actual	Budget	Commit

<tbody>
</tbody>

Taking three rows to do what I represented at one row and throwing another bunch of non-unique values into the mix. Row one is used for filters.
Is it still possible to pick the desired values from the spreadsheet?
Thanks and sorry for the mistake.
Rockfish

Is the difference in the headers? Could you create a sample illustrative of your data along with the results you want to see?

 

[Plastikman]

I believe it is also possible to use

=INDEX(D1:D5,MATCH("x"&"y"&"z",A1:A5&B1:B5&C1:C5,0))

instead of suggested

=INDEX(D1:D5,MATCH(1,(A1:A5="x")*(B1:B5="y")*(C1:C5="z"),0))

Ctrl+Shift+Enter is needed though as it still is an array formula.

 

[T. Valko]

Thank you for your insight. Have been working with this all day and it works great in the example I provided. Turns out I provided an oversimplified example and that breaks the solution all to pieces. The "huge honkin" budget sheet is actuall of the form

Jun	Jun	Jun	Jul	Jul	Jul	Q1	Q1	Q1	Q2	Q2	Q2
Actual	Budget	Commit	Actual	Budget	Commit	Actual	Budget	Commit	Actual	Budget	Commit

<TBODY>
</TBODY>

Taking three rows to do what I represented at one row and throwing another bunch of non-unique values into the mix. Row one is used for filters.
Is it still possible to pick the desired values from the spreadsheet?
Thanks and sorry for the mistake.
Rockfish

Could you possibly make up a SMALL sample file that demonstrates your data layout along with the desired results?

You can't directly attach a file in these forums but you can upload a file to some other site and then post a link to that file. You can use a free file hosting site if needed.

 

[Rockfish]

		Jun	Jun	Jun	Jul	Jul	Jul	Q1	Q1	Q1	Q2	Q2	Q2
		Actual	Budget	Commit	Actual	Budget	Commit	Actual	Budget	Commit	Actual	Budget	Commit
Belgium	Air Travel	15	20	12	72	76	18	73	50	67	58	31	83
	Other T&E	83	16	100	76	54	63	64	60	43	1	86	53
	Training	74	40	60	61	7	58	8	89	54	44	26	49
	Recruiting/Relocation	18	10	23	69	15	4	96	34	17	4	28	39
Canada	Air Travel	1	4	48	59	24	35	86	39	12	30	28	44
	Other T&E	22	98	95	53	24	15	38	68	49	54	92	74
	Training	22	88	34	97	81	28	6	87	59	74	55	98
	Recruiting/Relocation	30	44	13	6	90	33	90	43	37	74	35	90
Denmark	Air Travel	49	48	6	85	42	2	96	91	88	52	45	70
	Other T&E	25	10	89	1	61	4	25	55	94	20	11	51
	Training	19	20	29	27	45	14	37	73	57	46	96	37
	Recruiting/Relocation	18	22	50	89	49	39	94	4	23	45	16	47
Ecuador	Air Travel	51	36	51	61	37	62	11	19	62	78	25	18
	Other T&E	54	24	65	100	44	87	22	81	56	91	31	95
	Training	43	27	54	41	41	33	87	85	75	21	7	61
	Recruiting/Relocation	62	26	58	70	33	33	80	75	57	61	13	86

<COLGROUP><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5814" width=159><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><TBODY>
</TBODY>

 

[Aladin Akyurek]

		Jun	Jun	Jun	Jul	Jul	Jul	Q1	Q1	Q1	Q2	Q2	Q2
		Actual	Budget	Commit	Actual	Budget	Commit	Actual	Budget	Commit	Actual	Budget	Commit
Belgium	Air Travel	15	20	12	72	76	18	73	50	67	58	31	83
	Other T&E	83	16	100	76	54	63	64	60	43	1	86	53
	Training	74	40	60	61	7	58	8	89	54	44	26	49
	Recruiting/Relocation	18	10	23	69	15	4	96	34	17	4	28	39
Canada	Air Travel	1	4	48	59	24	35	86	39	12	30	28	44
	Other T&E	22	98	95	53	24	15	38	68	49	54	92	74
	Training	22	88	34	97	81	28	6	87	59	74	55	98
	Recruiting/Relocation	30	44	13	6	90	33	90	43	37	74	35	90
Denmark	Air Travel	49	48	6	85	42	2	96	91	88	52	45	70
	Other T&E	25	10	89	1	61	4	25	55	94	20	11	51
	Training	19	20	29	27	45	14	37	73	57	46	96	37
	Recruiting/Relocation	18	22	50	89	49	39	94	4	23	45	16	47
Ecuador	Air Travel	51	36	51	61	37	62	11	19	62	78	25	18
	Other T&E	54	24	65	100	44	87	22	81	56	91	31	95
	Training	43	27	54	41	41	33	87	85	75	21	7	61
	Recruiting/Relocation	62	26	58	70	33	33	80	75	57	61	13	86

<tbody>
</tbody>

Thanks... The row and column headers in your exhibit appear to have trailing spaces. If this is really the case in your sheet, try to run the TrimAll macro to remove them.

Sheet1, A1:N18...

		Jun	Jun	Jun	Jul	Jul	Jul	Q1	Q1	Q1	Q2	Q2	Q2
		Actual	Budget	Commit	Actual	Budget	Commit	Actual	Budget	Commit	Actual	Budget	Commit
Belgium	Air Travel	15	20	12	72	76	18	73	50	67	58	31	83
	Other T&E	83	16	100	76	54	63	64	60	43	1	86	53
	Training	74	40	60	61	7	58	8	89	54	44	26	49
	Recruiting/Relocation	18	10	23	69	15	4	96	34	17	4	28	39
Canada	Air Travel	1	4	48	59	24	35	86	39	12	30	28	44
	Other T&E	22	98	95	53	24	15	38	68	49	54	92	74
	Training	22	88	34	97	81	28	6	87	59	74	55	98
	Recruiting/Relocation	30	44	13	6	90	33	90	43	37	74	35	90
Denmark	Air Travel	49	48	6	85	42	2	96	91	88	52	45	70
	Other T&E	25	10	89	1	61	4	25	55	94	20	11	51
	Training	19	20	29	27	45	14	37	73	57	46	96	37
	Recruiting/Relocation	18	22	50	89	49	39	94	4	23	45	16	47
Ecuador	Air Travel	51	36	51	61	37	62	11	19	62	78	25	18
	Other T&E	54	24	65	100	44	87	22	81	56	91	31	95
	Training	43	27	54	41	41	33	87	85	75	21	7	61
	Recruiting/Relocation	62	26	58	70	33	33	80	75	57

<tbody>
</tbody>

Create a 4-row range on a convenient place with the following entries:

Air Travel

Other T&E

Training

Recruiting/Relocation

<tbody>
</tbody>

Select this range and name it Categories.

Sheet2, A:C, shows example processing...

		Q1
		Actual
Belgium	Training	8

<tbody>
</tbody>

C3, control+shift+enter, not just enter:

=INDEX(Sheet1!$C$3:$N$18,
  MATCH($A3,Sheet1!$A$3:$A$18,0)+(MATCH($B3,Categories,0)-1),
  MATCH(1,IF(Sheet1!$C$1:$N$1=$C$1,IF(Sheet1!$C$2:$N$2=C$2,1)),0))

As you see, it's a conceptually similar solution as before. This one has to do with two sets of column headers.

 

[Rockfish]

Thank you,
Rockfish

 

[Aladin Akyurek]

Thank you,
Rockfish

You are welcome. Thanks for providing feedback.

 

[Rockfish]

As with so many, I am interrupt-driven. Sorry for the terse Thank You. Will post additional comments and feedback when I get back on the primary stack.....
.....rockfish

 

[Rockfish]

Thanks... The row and column headers in your exhibit appear to have trailing spaces. If this is really the case in your sheet, try to run the TrimAll macro to remove them.
So I am working on this, but there are leading single quotes, then spaces, then the text to search on.
Example: ' Salary
One does not see the quote in the cell unless the cell is selected. Then it appears in the edit box and in the cell being edited. Does this affect what is being attempted here?

Sheet1, A1:N18...Snip....brevity. I renamed the sheets in question Sheet1 and Sheet2 to make things a little more uniform and streamlined. I also deleted row 1 which is/was blank in the example given and the real spreadsheet.

Create a 4-row range on a convenient place with the following entries:

Air Travel

Other T&E

Training

Recruiting/Relocation

<TBODY>
</TBODY>

Select this range and name it Categories. Rockfish>> Done and have tried them with the leading single quote & spaces and without. No Joy.

Sheet2, A:C, shows example processing...

		Q1
		Actual
Belgium	Training	8

<TBODY>
</TBODY>

C3, control+shift+enter, not just enter:

=INDEX(Sheet1!$C$3:$N$18,
  MATCH($A3,Sheet1!$A$3:$A$18,0)+(MATCH($B3,Categories,0)-1),
  MATCH(1,IF(Sheet1!$C$1:$N$1=$C$1,IF(Sheet1!$C$2:$N$2=C$2,1)),0))

As you see, it's a conceptually similar solution as before. This one has to do with two sets of column headers.

Looking at your code, line 1 appears to define the data source on Sheet1? Presuming yes so have modified that line to encapsulate the whole of the data on Sheet1. In reality the range is C3:AX1982. Is there a limit? Wondering why Columns A & B are omitted?

Line 2 seems to parsing colA and colB to find A3 and B3. Don't get the function of the Categories table though.
Recall that there are loads of rows for each country and the country is only in Column A once. Column B runs its full range of accounts, then Column A states another country and Column B runs its full range of accounts. Column A is mostly empty. I could do a fill-down for all blanks in Column A if that would help.

Line 3 seems to be parsing Row 1 & 2 in Sheet1 to match the row1 and row2 values above where the code is entered. Are all the $ present or required?

Cell C3 on Sheet2 contains:

=INDEX(Sheet1!$C$3:$AX$1982,
MATCH($A3,Sheet1!$A$3:$A$1982,0)+(MATCH($B3,Categories,0)-1),
MATCH(1,IF(Sheet1!$C$1:$AX$1=$C$1,IF(Sheet1!$C$2:$AX$2=C$2,1)),0))

I make sure to do the CSE each time I change anything. Unfortunately it returns #N/A.

Argh.
Rockfish

 

[Aladin Akyurek]

Looking at your code, line 1 appears to define the data source on Sheet1? Presuming yes so have modified that line to encapsulate the whole of the data on Sheet1. In reality the range is C3:AX1982. Is there a limit? Wondering why Columns A & B are omitted?

Line 2 seems to parsing colA and colB to find A3 and B3. Don't get the function of the Categories table though.
Recall that there are loads of rows for each country and the country is only in Column A once. Column B runs its full range of accounts, then Column A states another country and Column B runs its full range of accounts. Column A is mostly empty. I could do a fill-down for all blanks in Column A if that would help.

Line 3 seems to be parsing Row 1 & 2 in Sheet1 to match the row1 and row2 values above where the code is entered. Are all the $ present or required?

Cell C3 on Sheet2 contains:

=INDEX(Sheet1!$C$3:$AX$1982,
MATCH($A3,Sheet1!$A$3:$A$1982,0)+(MATCH($B3,Categories,0)-1),
MATCH(1,IF(Sheet1!$C$1:$AX$1=$C$1,IF(Sheet1!$C$2:$AX$2=C$2,1)),0))

I make sure to do the CSE each time I change anything. Unfortunately it returns #N/A.

Argh.
Rockfish

I'm afraid the quoting structure in your post is difficult to use...

Some comments:

1) Entries like ' Salary can be cleaned and it's better to do so. By the way, there is no need for prefixing a text entry with a single quote. Stray spaces thwart exact matching.

2) Creating a range housing categories should not be a problem (see the workbook to which I give a link below). We should avoid leading and trailing spaces.

Here is the workbook I used...

http://tinyurl.com/92wftzg