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

lauren garcia

New Member
Joined
Aug 10, 2019
Messages
12
what is the formula to match automatically the second value if the result of the first match is n/a or zero?

thank you!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Try:

Excel 2012
ABCDE
1ValueMatchListMatch
2a10b1
3a0
4cn/a
5x6
6e2
7a10
8y2

<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
CellFormula
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
A2

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>
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top