Function to find specific value and return

ERed1

Board Regular
Hi all,

So I need help writing a formula, not in vba, that looks at a number in column A, then looks at another sheet. Finds the value from column A in a list. If a value isn't found, it returns "Check". For example:

Sheet 1ABC
1
Dog
2Cat
3Frog

<tbody>
</tbody>
Sheet 2
ABC
1
DogFood
2CatWater
3DogNothing

<tbody>
</tbody>

So using the tables as an example, I need a formula in sheet 1 Column B that will look at column A, find "Dog" on sheet 2. If Dog has "nothing", in column B, then retutrn the value "check".

If that doesn't make sense I can try and describe it.
 

ERed1

Board Regular
It should just return a blank. I would also like it to do it for cat and frog too, if that makes sense.
 

Fluff

MrExcel MVP, Moderator
How about
=IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$B$10,2,0)),"",IF(VLOOKUP(A2,Sheet2!$A$2:$B$10,2,0)=0,"Check",""))
 

ERed1

Board Regular
I am pretty Sure I got it to work with what I need. Thanks for the help Fluff!
 

ERed1

Board Regular
What is the first part of the code doing?
"=IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$B$10,2,0)),"",...)
 

Fluff

MrExcel MVP, Moderator
If the value in A2 does not exist on sheet2 you will get a #N/A error, that is just checking for that
 

ERed1

Board Regular
Gotcha. I'm gonna see what I can do with it. Thanks for the help.
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top