Returning Yes, No, or #N/A when certain conditions are met

**tkdlance** · 09-18-2014, 02:26 PM

I came across another situation that I was hoping you could help me with. It's another look up match problem. The layout has 3 columns Person, Form, Location and Yes/No. I'm looking to see if the criteria is met the formula will return Yes No or #n/a

Person Form Location Yes/No
Steve C-20 USA
Steve C-19 USA
Steve C-28 USA
Steve C-30 USA
Steve C-22 USA
Steve C-23 USA Yes
Steve B-100 Brazil
Steve C-55 Brazil No
Jill C-22 Japan
Frank P-01 Mexico
Frank P-23 Mexico
Frank P-15 Mexico
Frank P-16 Mexico
Frank P-12 Mexico No
Frank P-01 German
Frank P-16 German
Frank P-15 German No
Frank P-08 German
Frank P-33 German

So if I'm looking for Steve, C-23, USA, Yes I want it to return Yes
So if I'm looking for Frank, P-15, German, No I want it to return No
So if I'm looking for Frank, P-33, German, blank I want it to return #N/A

**mrice** · 09-18-2014, 02:37 PM

Try

=IF(COUNTIFS(A2,"Steve",B2,"C-23",C2,"USA") = 1,"Yes",IF(COUNTIFS(A2,"Frank",B2,"P-15",C2,"German") = 1,"No",IF(COUNTIFS(A2,"Frank",B2,"P-33",C2,"German") = 1,NA())))

in D2 and copy down.

**tkdlance** · 09-18-2014, 02:45 PM

I'm looking for Yes or No or blank that is already there.

So if I'm looking for Steve, C-23, USA, Yes .... I want it to return Yes
So if I'm looking for Frank, P-15, German, No ... I want it to return No
So if I'm looking for Frank, P-33, German, blank ... I want it to return #N/A

Sorry I was just looking at this and it was a little confusing on what I was asking about.

**mrice** · 09-18-2014, 02:51 PM

OK - create a helper column with the three fields concatenated and use this for your look up.

**tkdlance** · 09-18-2014, 03:12 PM

if I do a Concatenate it would have to be a nested If Statement..

=IF(CONCATENATE(A17,B17,C17,D17)="SteveC-23USAYes","yes",IF(CONCATENATE(A17,B17,C17,D17)="SteveC-23USANo","No","#N/A"))

**tkdlance** · 09-18-2014, 03:21 PM

If I do a Concatenate it would have to be a nested If Statement..

=IF(CONCATENATE(A17,B17,C17,D17)="SteveC-23USAYes","yes",IF(CONCATENATE(A17,B17,C17,D17)="SteveC-23USANo","No","#N/A"))

My only issue is the workbook that I'm working on is looking up the Data in another workbook that I can't add an extra column to.

**ben_hensel** · 09-18-2014, 03:38 PM

I count a minimum of 3*17*5*3 = 765 combinations of data.

How do you want to handle the (765 - 3) / 765 = 99.6% of possibilities you haven't explicitly stated?

**Craig K.** · 09-18-2014, 03:50 PM

Assuming the table you're referencing starts at cell A1 (including the column headers) and you're using reference cells for the three variables (Person, Form, Location) in the workbook you're placing the formula in, you can try this:

Formula:

Please Login or Register  to view this content.

**tkdlance** · 09-18-2014, 03:51 PM

This is only a sample of the Data that I'm working with. There are over 10,000 names and for each name there are 200 Forms, with locations all over the world. Some have Yes some have No and some aren't filled in at all. So I'm just trying to find out if the person has looked at or filled out certain forms.

**Speshul** · 09-18-2014, 04:05 PM

make listbox dropdowns for your search

in F1, G1, H1

untested, but[B]
=THISFORMULA(Didn't work)

see below post

**Speshul** · 09-18-2014, 04:33 PM

Here I tested this one, this works.

Use Control + Shift + Enter to enter this Array Formula
=INDEX(D1:D13,MATCH(F1&G1&H1,A1:A13&B1:B13&C1:C13,0)))

Use F1, G1, and H1 as the three fields to look for.

Note, if you have duplicates in there (three fields are identical in more than one row) it will return the first one it finds.

Adjust the size as needed obviously, i only tested on 13 rows. For luck.