match the second value if the result of first match is N/A

[lauren garcia]

what is the formula to match automatically the second value if the result of the first match is n/a or zero?

thank you!

 

[Eric W]

Try:

Excel 2012

	A	B	C	D	E
1	Value	Match		List	Match
2	a	10		b	1
3				a	0
4				c	n/a
5				x	6
6				e	2
7				a	10
8				y	2

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6
​

Worksheet Formulas Cell Formula B2 =INDEX($E:$E,AGGREGATE(15,6,ROW($D$2:$D$8)/(($D$2:$D$8=A2)*($E$2:$E$8<>0)*($E$2:$E$8<>"n/a")),1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

This will in fact find the first matching value that isn't a 0 or N/A, even if it's the 2nd or 3rd or 4th match.

 

[lauren garcia]

is this an array formula?

 

[lauren garcia]

is this an array formula?

 

[Eric W]

Depends on how you look at it. Technically it is, since it requires looking at a range of cells. But the AGGREGATE function has array functionality built in, so you don't need to enter the formula using Control+Shift+Enter. If you have some aversion to array formulas, I could probably rewrite it as a lookup inside a lookup. But to do that, it would help to know what kind of data you're looking at. Text, numbers, integers? Are the values constants or created by formulas?

 

[lauren garcia]

hi Eric, what a result I want is when I put a site for example 2100 on row A and it will result on row F, but when the result on row F is n/a, the row A will automatically match the second value. i have 3 sheets use

 

[Eric W]

I'm afraid I don't understand your latest post. Have you tried the formula I suggested? If it doesn't work, could you show an example?

 

[lauren garcia]

I'm sorry if my question is confusing

when i paste the 'name of the customer' which is in row D, the row A will automatically lookup the site that is correspond to the place of the customer, and the row F which has the quantity available of each material will also automatically lookup of the quantity available of the site.

i have 3 sheets, my main template, template for stocks, template for the lookup site

<tbody>
</tbody>

 

[lauren garcia]

https://docs.zoho.com/sheet/open/fzuh970e4ffa3650a4eaa81ede5f4c71d08e2/sheets/Sheet1/ranges/A2

that is my main template

https://docs.zoho.com/sheet/open/fzuh970e4ffa3650a4eaa81ede5f4c71d08e2/sheets/LOOKUP SITE/ranges/B8

and this is my lookup site sheet

 

[Eric W]

Sorry, I don't have a Zoho account, so I can't see your sheets. There are other file sharing or picture sharing sites which don't require memberships.