VLOOKUP MULTIPLE COLUMNS

[TUhl]

With the knowlege that I have of Excel, VLOOKUP is the best function I know of to look up and match to other data in a workbook. If there is another way to do this please let me know.

I need to use 2 columns in a data array to match in order to be supplied with the correct item number that is also in the data array (in a different column). This is what I have tried:

=VLOOKUP(G2:H2,data array,1,false)

This is the part I am having trouble with

Help?!

Thank you

Toni

 

[TUhl]

Copy of 9_625 2007.xls
	A	B	C	D	E	F	G
1	ITEM#	CommonWellAFE	CommonWellName	DisctrictDescription	TRansferDate	ItemSize(in)	Grade
2		WT8583	CTR23-3-597	PICEANCEHL	2/17/2007	9.625	J-55
3		WT8583	CTR23-3-597	PICEANCEHL	2/17/2007	9.625	J-55
4		WT8583	CTR23-3-597	PICEANCEHL	3/7/2007	9.625	J-55
5		WT8583	CTR23-3-597	PICEANCEHL	3/7/2007	9.625	J-55
6		WT8619	CTR533-3-597	PICEANCE	3/8/2007	9.625	J-55
7		WT8619	CTR533-3-597	PICEANCE	3/8/2007	9.625	J-55
8		WT8619	CTR533-3-597	PICEANCE	3/9/2007	9.625	J-55
9		WT8619	CTR533-3-597	PICEANCE	3/9/2007	9.625	J-55
10		WT8395	FEDERALBCU23-22-198	PICEANCEHL	1/11/2007	9.625	J-55
11		WT8395	FEDERALBCU23-22-198	PICEANCEHL	2/1/2007	9.625	J-55
12		WT8395	FEDERALBCU23-22-198	PICEANCEHL	1/10/2007	9.625	J-55
13		WT2978	FEDERALRGU12-10-298D	PICEANCEHL	1/31/2007	9.625	J-55
14		WT2978	FEDERALRGU12-10-298D	PICEANCEHL	1/31/2007	9.625	J-55
15		WT2978	FEDERALRGU12-10-298D	PICEANCEHL	2/1/2007	9.625	H-40
16		WT2978	FEDERALRGU12-10-298D	PICEANCEHL	1/14/2007	9.625	H-40
17		WT2980	FEDERALRGU14-28-198	PICEANCE	2/20/2007	9.625	J-55
JAN-MAR

Ok, I need an Item # to appear in the A column when the Item size AND the Grade match the data array (I will post that in a second). If you look down Column F you will notice that the Item Size is all the same but the Grade is different in a few fields.

 

[TUhl]

Copy of 9_625 2007.xls
	A	B	C	D
1	ITEMSIZE	GRADE	ITEM#	WEIGHT
2	1.9	J-55	083055.A	2.75
3	2.375	J-55	083059.A	4.7
4	2.375	N-80	083127.A	4.7
5	4.5	I-80	083007.A	11.6
6	4.5	P-110	083008.A	11.6
7	4.5	I-100	083487.A	11.6
8	9.625	J-55	083041.A	36
9	9.625	H-40	083045.A	32.3
10	9.625	J-55	083411.A	40
ITEM #S

here is the data array

 

[Oaktree]

Same idea as the previous formula I suggested:

A2=INDEX('Item #S'!C$2:C$10,MATCH(1,('Item #S'!A$2:A$10=F2)*('Item #S'!B$2:B$10=G2),0)), confirmed with CTRL+SHIFT+ENTER

 

[TUhl]

Thank you soooo much! It worked. If you don't mind will you explain what the formula is doing incase i have to do it again on another spreadsheet?

 

[Oaktree]

It's really the same formula I suggested in my Scooby Doo example, just modified for your ranges/sheet names...

Have a look at the INDEX and MATCH formulas in Excel help to understand how those work.

Then, the only curveball is the ('Item #S'!A$2:A$10=F2)*('Item #S'!B$2:B$10=G2) part....Since Excel treats TRUE as 1 and FALSE as 0, this part will return an array of 1's and 0's according to whether each column A/column B combination in rows 2:10 meet both criteria (to receive a 1) or does not meet at least one criterion (to receive a 0).

Since ('Item #S'!A$2:A$10=F2)*('Item #S'!B$2:B$10=G2) returns an array (as opposed to a single value), you need the CTRL+SHIFT+ENTER confirmation.

Does that help?

 

[TUhl]

It does! Thank you so much...How would it change if I needed to look at 3 columns ie size, weight and grade?

 

[yogender]

thanks for giving reply]