# vlookup on more than 1 coulmn to pull single value

#### lundbhaiz

##### Active Member
hi's

this my sheet1-

Excel 2010
ABCDE
6DesignatorMaterial NumberValueVerified Material Number
7C61622321u/25V#N/A001 10% 25VDC X7R
8C719020210n/25V010n 5% 25VDC X7R
9C11100823986n8/50V006n8 5% 50VDC X7R
10C201622321u/25V001 10% 25VDC X7R
11C231622321u/25V001 10% 25VDC X7R
12C24190182100n/25V100n 20% 25VDC X7R
13C26190182100n/25V100n 20% 25VDC X7R
14C27190182100n/25V100n 20% 25VDC X7R
15C2919020210n/25V010n 5% 25VDC X7R
16C30190182100n/25V100n 20% 25VDC X7R
17C31190182100n/25V100n 20% 25VDC X7R
18C331918721n/25V001n 2% 25VDC C0G
19C341622321u/25V001 10% 25VDC X7R
20C3519020210n/25V010n 5% 25VDC X7R
21C36168742470n/25V470n 5% 25VDC X7R
22C38190182100n/25V100n 20% 25VDC X7R
23C39169882680n/16V680n 10% 16VDC X7R
24C42190182100n/25V100n 20% 25VDC X7R
25C43190182100n/25V100n 20% 25VDC X7R
26C44190182100n/25V100n 20% 25VDC X7R
Sheet1
Cell Formulas
RangeFormula
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
DEFGHIJKLMNOPQRS
1Component numberItem Text Line 1Item text line 2Comp. Qty (CUn)Component unit
2175882C CE010n ±10% 630VDC X7R 1206C141PC
3175872C CE100p ± 5% 3000VDC C0G 1808C15C162PC
410091197C CE220p ±10% 630VDC C0G 1206C19C492PC
5191872C CE001n ± 2% 25VDC C0G 603C21C512PC
6190182C CE100n ±20% 25VDC X7R 603C22C28C32C45C555PC
7162232C CE001µ ±10% 25VDC X7R 805C25C37C573PC
810082419C CE220p ± 2% 50VDC C0G 603C40C412PC
910082395C CE002n2 ± 5% 50VDC X7R 805C52C532PC
10194802V ICPIC12F508-E/SN SO8N31PC
11188072V IC74V1T32 SOT23-5LN51PC
12188062V IC74V1T00 SOT23-5LN61PC
13174652R ME005R1 ± 5% 200V 0.25W 1206R181PC
14150882R ME100K ± 1% 200V 0.25W 1206R34R35R363PC
15196402R ME051K ± 1% 50V 0.1W 603R43R612PC
1610087072R ME002K2 ± 1% 50V 0.1W 603R461PC
17190282R ME075K ± 1% 50V 0.1W 603R48R672PC
18167592R ME200K ± 1% 50V 0.1W 603R53R1202PC
19197132R ME022R ± 1% 150V 0.125W 805R561PC
20196432R ME006K8 ± 1% 50V 0.1W 603R601PC
21191792R ME150K ± 1% 50V 0.1W 603R641PC
22196362R ME220R ± 1% 50V 0.1W 603R651PC
23177972R ME007K5 ± 5% 200V 0.25W 1206R661PC

<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.

Last edited:

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
[...]

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:
Rich (BB code):
``````=IFERROR(INDEX(Sheet2!\$D\$2:\$D\$23,MAX(IF(ISNUMBER(SEARCH(A7,Sheet2!\$F\$2:\$Z\$23)),

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.

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".

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.

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.

Replies
5
Views
473
Replies
12
Views
254
Replies
11
Views
490
Replies
1
Views
206
Replies
3
Views
242

1,206,718
Messages
6,074,493
Members
446,072
Latest member
OrangeYellow

### 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.

### Which adblocker are you using?

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

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