VLOOKUP 2 values

[Nanaia]

Is it possible to use VLOOKUP and have it search for 2 values to find a result?
To explain, I want to be able to have the VLOOKUP formula in G3 look at A3 & B3 and find result in the array on another sheet. And this description sounds muddled to my coffee deprived brain so I'm going to attempt to add to it without making it more confusing.

The following is my array on a secondary worksheet tab. On the primary worksheet I have it set up so we enter the drawing number and the face size and am using VLOOKUP to give me the online code listed in column C here. I realized after the fact that I need it to use BOTH the drawing number AND the face size in order to get the right code. I want it to kick out the message "NEEDS PROGRAM" if there isn't a code. Is there a way to combine that in VLOOKUP?

DRW#	FACE	Online Code
1	16	"ABCD\DWG_1.DOC//FaceX 108 FaceZ 16"
1	24	"ABCD\DWG_1.DOC//FaceX 108 FaceZ 24"
2	8	"ABCD\DWG_2.DOC//FaceX 108 FaceZ 8"
2	12	"ABCD\DWG_2.DOC//FaceX 108 FaceZ 12"
18	20	"ABCD\DWG_18.DOC//FaceX 108 FaceZ 20"
18	16	"ABCD\DWG_18.DOC//FaceX 108 FaceZ 16"

<tbody>
</tbody>

 

[Scott T]

is this what you want?

Excel 2010

	A	B	C	D	E	F	G
3	1	24					"ABCD\DWG_1.DOC//FaceX 108 FaceZ 24"

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula G3 {=IFERROR(INDEX(Sheet4!C2:C7,MATCH(Sheet3!A3&Sheet3!B3,Sheet4!A2:A7&Sheet4!B2:B7,0)),"Needs Program")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Excel 2010

	A	B	C
1	DRW#	FACE	Online Code
2	1	16	"ABCD\DWG_1.DOC//FaceX 108 FaceZ 16"
3	1	24	"ABCD\DWG_1.DOC//FaceX 108 FaceZ 24"
4	2	8	"ABCD\DWG_2.DOC//FaceX 108 FaceZ 8"
5	2	12	"ABCD\DWG_2.DOC//FaceX 108 FaceZ 12"
6	18	20	"ABCD\DWG_18.DOC//FaceX 108 FaceZ 20"
7	18	16	"ABCD\DWG_18.DOC//FaceX 108 FaceZ 16"

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Sheet4​

 

[Nanaia]

Potentially..... I'm having trouble getting it to work using my sheets though. Sheet names are TEST (the primary sheet that the formula is located on-your sheet 3 I believe), and the array on mine is located on a tab named FOLDED. When I replace your sheet names with mine I get an "N/A" error.

 

[Nanaia]

I went through it again and got rid of the "N/A" error. Now it tells me "NEEDS PROGRAM" even if the A & C are there and an associated program is listed in column C. Any ideas what I'm doing wrong?

 

[Scott T]

This is an array formula and needs Control + Shift+ Enter not just enter. If you do not use CSE you will get an error and the iferror will return NEEDS PROGRAM.

 

[Nanaia]

I've never used that technique before. I think I got it to work. Thanks!

 

[Nanaia]

Do the columns need to be directly next to each other? I'm tweaking this formula to be used in a similar situation and keep getting an #NA error. The INDEX array column (shown here as sheet4 column C) is sheet 2 column I, and the MATCH look up values are located in sheet 1 column B & C, the MATCH array is sheet 2 column B & D. Have I exceeded the abilities of the formula? Or just my own?

 

[Scott T]

The columns do not need to be next to each other

Excel 2010
	A	B	C	D	E	F	G
3	1		24				"ABCD\DWG_1.DOC//FaceX 108 FaceZ 24"
Sheet3

Cell Formulas
Range	Formula
G3	{=IFERROR(INDEX(Sheet4!D2:D7,MATCH(Sheet3!A3&Sheet3!C3,Sheet4!A2:A7&Sheet4!C2:C7,0)),"Needs Program")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Excel 2010
	A	B	C	D
1	DRW#		FACE	Online Code
2	1		16	"ABCD\DWG_1.DOC//FaceX 108 FaceZ 16"
3	1		24	"ABCD\DWG_1.DOC//FaceX 108 FaceZ 24"
4	2		8	"ABCD\DWG_2.DOC//FaceX 108 FaceZ 8"
5	2		12	"ABCD\DWG_2.DOC//FaceX 108 FaceZ 12"
6	18		20	"ABCD\DWG_18.DOC//FaceX 108 FaceZ 20"
7	18		16	"ABCD\DWG_18.DOC//FaceX 108 FaceZ 16"
Sheet4

You do need to use the same order. the lookup value is drw# (A3) and then Face (C3). So the lookup array needs to be in the same order column A (Drw#) then column C (Face) .

MATCH([COLOR=#FF0000]Sheet3!A3&Sheet3!C3[/COLOR],[COLOR=#006400]Sheet4!A2:A7&Sheet4!C2:C7[/COLOR],0)