# Trouble with an =IF(ISNA(VLOOKUP Formula

Copper Contributor

# Trouble with an =IF(ISNA(VLOOKUP Formula

I have with multiple sheets in a workbook with unique custom number (0000000000) and trying to verify if the main sheet array has that same  custom number. If the custom number is not available in the search array I need a "No Photo", if it is I need a "Yes Photo.

I see custom numbers in the aray and can get "Yes Photo" but I also get "Yes Photo" when I physically verify.

formula as typed =IF(ISNA(VLOOKUP(D1,'Silhouette Master List'!C\$1:C\$1082,FALSE)),"No Photo","Yes Photo")

same results if edited as =IF(ISNA(VLOOKUP(D1,'Silhouette Master List'!C\$1:C\$1082,TRUE)),"No Photo","Yes Photo")

Been working for over 24 hours in a critical short deadline situation.

6 Replies

# Re: Trouble with an =IF(ISNA(VLOOKUP Formula

Your VLOOKUP() is missing the third argument (column index number).

# Re: Trouble with an =IF(ISNA(VLOOKUP Formula

@Detlef Lewin It has the 3rd argument in the form of C1:C1082 from the Silhouette Master. The issue is when I go into verify a custom number from the LookUp sheet on the Array sheet and the custom number is not there, and I should be receiving a "No Photo" as my result of the lookup, but I am not, it states "Yes Photo".  There are other custom numbers in the lookup that are also on the array and they are coming up as "No Photo" and should be coming up as "Yes Photo". I wish I could attach the images I captured of the lookup and the array from my computer screen.

# Re: Trouble with an =IF(ISNA(VLOOKUP Formula

In VLOOKUP(D1,'Silhouette Master List'!C\$1:C\$1082,FALSE), VLOOKUP has 3 arguments:

1. D1
2. 'Silhouette Master List'!C\$1:C\$1082
3. FALSE

Your lookup range has only one column, and FALSE should be the 4th argument, not the 3rd one...

# Re: Trouble with an =IF(ISNA(VLOOKUP Formula

Hans'
I think I understand a little? I am a yearbook instructor and am trying to schedule the students who do not have their photos taken based on an original "Silhouette Master" file sent to me in Numbers and converted to Excel. I have 16 teachers class roster pages originally downloaded from our school system as excel (old version and converted to the new excel format).

Here is the current full lookup formula with 11 "No Photo" results on one of the teachers sheets:
=IF(ISNA(VLOOKUP(D1,'Silhouette Master List'!C1:C\$1082,TRUE)),"No Photo","Yes Photo")

If I flip the IF Statement conditions I get 132 "No Photo results:
=IF(ISNA(VLOOKUP(D1,'Silhouette Master List'!C1:C\$1082,FALSE)),"Yes Photo","No Photo")

However, when I go to the Silhouette Master sheet to verify the VLOOKUP, I still see names in there that have image file names and are being identified as "No Photo" and at the same time i don't see student names who are identified as having a photo.

I keep looking at my posts and questions and see I am not being a good technician and providing enough of the data or full formulas. But I do appreciate the help and advice I am receiving.

# Re: Trouble with an =IF(ISNA(VLOOKUP Formula

You just want to check whether a value is in a list.

``=IF(COUNTIF('Silhouette Master List'!C1:C\$1082,D1);"Yes Photo";"No Photo")``

# Re: Trouble with an =IF(ISNA(VLOOKUP Formula

Hans,
I think I got it, based on your suggestion of the 4th argument. The Conditional Formatting is now lighting up Red for "No Photo". Thank you so very much.