VLookUp within a VLookUp

[maeyks]

I have another question, what if i have the following data:

Request number Item Code Item Quanity
001 1234 Water 100
001 1122 Flour 200
001 5432 Sugar 6
001 1235 Salt 2
001 1334 Yeast 5
001 1432 Margarine 22
002 1234 Water 200
002 1122 Flour 400
002 5432 Sugar 12
002 1235 Salt 4
002 1334 Yeast 10
002 1432 Margarine 44
003 1234 Water 500
003 1122 Flour 1000
003 5432 Sugar 30
003 1235 Salt 10
003 1334 Yeast 25
003 1432 Margarine 110


This is on the first tab (A). Then i will create a second tab (b). Wherein on tab B, if i will select the request number 003 and placed item code number 1334, it will return the value 25 and not 5 or 10

Or, if i will select the request number 002 and placed item code 1235, it will return the value 4 and not 10 or 2.

Can this be answered by vlookup? this is the example what i call, "vlookup, within a vlookup." hope someone can help me on this.

many thanks to the one who can me..

 

[escott]

You can do a Vlookup inside a vloopup, but I don't believe you can do it in the table array without taking it to VBA. The easy wasy would be to add a column to the left and CONCATENATE the two values and vLookup off that to your two input values.

=CONCATENATE(E10,F10)

=CONCATENATE(003,1234)

=VLOOKUP(CONCATENATE(value1,value2),E10:G27,3,FALSE) E new column you created

 

[T. Valko]

I have another question, what if i have the following data:

Request number Item Code Item Quanity
001 1234 Water 100
001 1122 Flour 200
001 5432 Sugar 6
001 1235 Salt 2
001 1334 Yeast 5
001 1432 Margarine 22
002 1234 Water 200
002 1122 Flour 400
002 5432 Sugar 12
002 1235 Salt 4
002 1334 Yeast 10
002 1432 Margarine 44
003 1234 Water 500
003 1122 Flour 1000
003 5432 Sugar 30
003 1235 Salt 10
003 1334 Yeast 25
003 1432 Margarine 110


This is on the first tab (A). Then i will create a second tab (b). Wherein on tab B, if i will select the request number 003 and placed item code number 1334, it will return the value 25 and not 5 or 10

Or, if i will select the request number 002 and placed item code 1235, it will return the value 4 and not 10 or 2.

Can this be answered by vlookup? this is the example what i call, "vlookup, within a vlookup." hope someone can help me on this.

many thanks to the one who can me..

Try this array formula**.

Let's assume your dat is on Sheet1 in the range A2:C19.

On some other sheet:

• A2 = some request number
• B2 = some item code

Then, this array entered** formula in C2:

=INDEX(Sheet1!C2:C19,MATCH(1,IF(Sheet1!A2:A19=A2,IF(Sheet1!B2:B19=B2,1)),0))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[snoopyhr]

or this approach:

Excel Workbook
	F	G	H	I	J	K	L
1	Request	Item No.	Item Code	Item Qty		Request	3
2	001	1234	Water	100		Item No	1334
3	001	1122	Flour	200		Count	1
4	001	5432	Sugar	6		Item Qty	25
5	001	1235	Salt	2
6	001	1334	Yeast	5
7	001	1432	Margarine	22
8	002	1234	Water	200
9	002	1122	Flour	400
10	002	5432	Sugar	12
11	002	1235	Salt	4
12	002	1334	Yeast	10
13	002	1432	Margarine	44
14	003	1234	Water	500
15	003	1122	Flour	1000
16	003	5432	Sugar	30
17	003	1235	Salt	10
18	003	1334	Yeast	25
19	003	1432	Margarine	110
Sheet7

#VALUE!

 

[maeyks]

wow, i see that it is quite complicated...however, how can i copy the formula (array)? if i try to copy it (Ctrl+c), the cell reference is changed...

 

[T. Valko]

wow, i see that it is quite complicated...however, how can i copy the formula (array)? if i try to copy it (Ctrl+c), the cell reference is changed...

I can't tell who you're replying to.

Make the references to the table data absolute:

=INDEX(Sheet1!$C$2:$C$19,MATCH(1,IF(Sheet1!$A$2:$A$19=A2,IF(Sheet1!$B$2:$B$19=B2,1)),0))

Don't forget, array entered!