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
Value	Match	List	Match
a	b
a
c	n/a
x
e
a
y

<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>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

</tbody>

Sheet6
​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: <a href="]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: <a href="]B2
[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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
[TABLE="width: 1039"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

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