Vlookup two values to give a third

[Tucker92]

I have a database and i am am trying to use vlookup or index and match to find the quantity of my parts

I have to look for two different numbers and then when it finds a row with both of them it will return a third number from that row

I have been trying to use this but it doesnt seem to be working
=INDEX(Table2[EXTENDED_USEAGE],MATCH(D2,Table2[TOP_LEVEL],0),MATCH(A2,Table2[CSS_ITEM],0))

Can anyone help me and tell me why

Thank you

 

[oldbrewer]

color	size	number	helper
red	s	4	1
blue	m	5			challenge
green	m	6			identify every row with red and s
pink	l	7
red	l	8
blue	l	9			I used row number to mark them
green	size	4
pink	m	5			easy now to add the 4 and 7
red	m	6			or to list them separately
blue	l	7
green	l	8
pink	l	9
red	s	7	13
blue	m	5
green	m	6
pink	l	7

<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Tucker92]

217207	ettjtrtgj	1	325645	ryghjrtj	rtjrfthjj
217207	erh6ujtd	1	458963	rtyjdfhdew	yjyhgjt

<tbody>
</tbody>

I need to use the 2 red numbers to search the other sheet to get the green number but as you can see there are many of the second number to go with a recurring 1st number.

Then the first number will change and there are another set of second numbers to go with it

 

[Marcelo Branco]

I have a database and i am am trying to use vlookup or index and match to find the quantity of my parts

I have to look for two different numbers and then when it finds a row with both of them it will return a third number from that row

I have been trying to use this but it doesnt seem to be working
=INDEX(Table2[EXTENDED_USEAGE],MATCH(D2,Table2[TOP_LEVEL],0),MATCH(A2,Table2[CSS_ITEM],0))

Can anyone help me and tell me why

Thank you

Try this array formula
=INDEX(Table2[EXTENDED_USEAGE],MATCH(1,(Table2[TOP_LEVEL]=A2)*(Table2[CSS_ITEM]=D2),0))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Tucker92]

ADVERTISEMENT

I tried that but it only worked on some of them not all of them

 

[Tucker92]

Sorry should have said that the green number in my example isnt always a 1

 

[Marcelo Branco]

oops...
New version
=INDEX(Table2[EXTENDED_USEAGE],MATCH(1,(Table2[TOP_LEVEL]=D2)*(Table2[CSS_ITEM]=A2),0))
Ctrl+Shift+Enter

M.

 

[Marcelo Branco]

Sorry should have said that the green number in my example isnt always a 1

I know the number isn't always a 1.
Try the array formula in post 7 - the formula should be confirmed with Ctrl+Shift+Enter simultaneously.

M.