Function to find specific value and return

[ERed1]

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sheet 1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Dog[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Cat
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Frog[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sheet 2
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Dog[/TD]
[TD]Food[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Cat[/TD]
[TD]Water[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Dog[/TD]
[TD]Nothing[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Fluff]

What should happen if dog does have something in col b on sheet2?

 

[ERed1]

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

 

[Fluff]

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]

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

 

[Fluff]

Glad to help & thanks for the feedback

 

[ERed1]

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

 

[Fluff]

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

 

[ERed1]

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

 

[Fluff]

You're welcome