vlookup on more than 1 coulmn to pull single value

[lundbhaiz]

hi's

this my sheet1-

Excel 2010
	A	B	C	D	E
6	Designator	Material Number	Value	Verified Material Number
7	C6	162232	1u/25V	#N/A	001 10% 25VDC X7R
8	C7	190202	10n/25V		010n 5% 25VDC X7R
9	C11	10082398	6n8/50V		006n8 5% 50VDC X7R
10	C20	162232	1u/25V		001 10% 25VDC X7R
11	C23	162232	1u/25V		001 10% 25VDC X7R
12	C24	190182	100n/25V		100n 20% 25VDC X7R
13	C26	190182	100n/25V		100n 20% 25VDC X7R
14	C27	190182	100n/25V		100n 20% 25VDC X7R
15	C29	190202	10n/25V		010n 5% 25VDC X7R
16	C30	190182	100n/25V		100n 20% 25VDC X7R
17	C31	190182	100n/25V		100n 20% 25VDC X7R
18	C33	191872	1n/25V		001n 2% 25VDC C0G
19	C34	162232	1u/25V		001 10% 25VDC X7R
20	C35	190202	10n/25V		010n 5% 25VDC X7R
21	C36	168742	470n/25V		470n 5% 25VDC X7R
22	C38	190182	100n/25V		100n 20% 25VDC X7R
23	C39	169882	680n/16V		680n 10% 16VDC X7R
24	C42	190182	100n/25V		100n 20% 25VDC X7R
25	C43	190182	100n/25V		100n 20% 25VDC X7R
26	C44	190182	100n/25V		100n 20% 25VDC X7R
Sheet1

Cell Formulas
Range	Formula
D7	=LOOKUP(9.99999999999999E+307,CHOOSE({3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22},0, VLOOKUP(A7,Sheet2!$D:$Z,1,0)))

this is my sheet2-

Excel 2010

	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Component number		Item Text Line 1											Item text line 2	Comp. Qty (CUn)	Component unit
2	175882	C CE010n ±10% 630VDC X7R 1206	C14												1	PC
3	175872	C CE100p ± 5% 3000VDC C0G 1808	C15	C16											2	PC
4	10091197	C CE220p ±10% 630VDC C0G 1206	C19	C49											2	PC
5	191872	C CE001n ± 2% 25VDC C0G 603	C21	C51											2	PC
6	190182	C CE100n ±20% 25VDC X7R 603	C22	C28	C32	C45	C55								5	PC
7	162232	C CE001µ ±10% 25VDC X7R 805	C25	C37	C57										3	PC
8	10082419	C CE220p ± 2% 50VDC C0G 603	C40	C41											2	PC
9	10082395	C CE002n2 ± 5% 50VDC X7R 805	C52	C53											2	PC
10	194802	V ICPIC12F508-E/SN SO8	N3												1	PC
11	188072	V IC74V1T32 SOT23-5L	N5												1	PC
12	188062	V IC74V1T00 SOT23-5L	N6												1	PC
13	174652	R ME005R1 ± 5% 200V 0.25W 1206	R18												1	PC
14	150882	R ME100K ± 1% 200V 0.25W 1206	R34	R35	R36										3	PC
15	196402	R ME051K ± 1% 50V 0.1W 603	R43	R61											2	PC
16	10087072	R ME002K2 ± 1% 50V 0.1W 603	R46												1	PC
17	190282	R ME075K ± 1% 50V 0.1W 603	R48	R67											2	PC
18	167592	R ME200K ± 1% 50V 0.1W 603	R53	R120											2	PC
19	197132	R ME022R ± 1% 150V 0.125W 805	R56												1	PC
20	196432	R ME006K8 ± 1% 50V 0.1W 603	R60												1	PC
21	191792	R ME150K ± 1% 50V 0.1W 603	R64												1	PC
22	196362	R ME220R ± 1% 50V 0.1W 603	R65												1	PC
23	177972	R ME007K5 ± 5% 200V 0.25W 1206	R66												1	PC

<tbody>
</tbody>

Sheet2​

problem-

i am trying to vlookup cell A7 on sheet1 in sheet2 columns F to Z. the value in cell A7 of sheet1 has to be matched in any cells of sheet 2 from column F to column Z (the screen does not show columns till Z on sheet2 because of message hanging). i want to pull value from column D in sheet2 to cell D7 in sheet1. i am trying this formula but cannot get it working for some reason. is there some other formula i can try ? or some show me what i doing is something wrong plz. plz help. thanks you for kind helps.

 

[Aladin Akyurek]

[...]

problem-

i am trying to vlookup cell A7 on sheet1 in sheet2 columns F to Z. the value in cell A7 of sheet1 has to be matched in any cells of sheet 2 from column F to column Z (the screen does not show columns till Z on sheet2 because of message hanging). i want to pull value from column D in sheet2 to cell D7 in sheet1. i am trying this formula but cannot get it working for some reason. is there some other formula i can try ? or some show me what i doing is something wrong plz. plz help. thanks you for kind helps.

Try...

D7, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet2!$D$2:$D$23,MAX(IF(ISNUMBER(SEARCH(A7,Sheet2!$F$2:$Z$23)),
  ROW(Sheet2!$F$2:$Z$23)-ROW(Sheet2!$F$2)+1,-9.99999999999999E+307))),"Not Found")

 

[lundbhaiz]

fantastic ! hi dears. can plz explain me how do formula work in short plz. it will helps me to understand the formula better so i can learn. thanks you aladin.

 

[Aladin Akyurek]

fantastic ! hi dears. can plz explain me how do formula work in short plz. it will helps me to understand the formula better so i can learn. thanks you aladin.

The IF bit looks for the target value (A7) in the target range and in case of a hit it returns a 'corrected' row number, otherwise it returns a large negative number (a constant Excel knows). The surrounding MAX returns the largest row number from the set IF gathers. Passed to INDEX, we get the desired outcome at that row from column D in the target range. In case of no hit, we get a #VALUE! error IFERROR turns into "Not Found".

 

[lundbhaiz]

oh so sweet Aladin. it will take my time to understand this explaination because i will learn formula with evaluate formula option in excel but at least i get what is coming from where. thanks you very much dear.

 

[Aladin Akyurek]

oh so sweet Aladin. it will take my time to understand this explaination because i will learn formula with evaluate formula option in excel but at least i get what is coming from where. thanks you very much dear.

You are welcome. Thanks for providing feedback.