# Pass/fail result from data table

1. ## Pass/fail result from data table

I have attached the document i am working on. I will be entering a Ref and 2 results (A & B). I would then like it to be able to automatically reference the data on sheet 2 to see if it passes or fails. How this is best done I'm not sure. I had thought of a check box but this may not be the best method. Green or red colour would be great. May not need the pass and fail column?
Any help would be fantastic as i have been sat staring for a long time with no real solution.
Many thanks

2. ## Re: What formula??

HI..

Use (VLOOKUP(\$H2,Sheet1!\$B\$2:\$E\$24,3,0)) for A & B results in next sheet and pls let me know wht is the criteria for pass & fail?? is there any number decided like for e.g."100",if exceeds, passes or fails??

Please find the attached sheet for vlookup

3. ## Re: What formula??

Hi,
Pass is anything between and including the lower & upper limits on both A & B which i will be inputting

4. ## Re: What formula??

You don't need BOTH columns for Pass and Fail, they're mutually exclusive.

=IF(AND(D2>=VLOOKUP(B2,Sheet2!A\$2:E\$29,2,0),D2<=VLOOKUP(B2,Sheet2!A\$2:E\$29,3,0),E2>=VLOOKUP(C2,Sheet2!A\$2:E\$29,4,0),E2<=VLOOKUP(C2,Sheet2!A\$2:E\$29,5,0)),"Pass","Fail")

5. ## Re: What formula??

Where would that formula go? I have placed it in the pass column and it states #N/A

6. ## Re: What formula??

Oops! My mistake, try this instead

in Sheet1!F2
=IF(AND(D2>=VLOOKUP(B2,Sheet2!A\$2:E\$29,2,0),D2<=VLOOKUP(B2,Sheet2!A\$2:E\$29,3,0),E2>=VLOOKUP(B2,Sheet2!A\$2:E\$29,4,0),E2<=VLOOKUP(B2,Sheet2!A\$2:E\$29,5,0)),"Pass","Fail")
and copy down the column for each entry you have

7. ## Re: What formula??

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

8. ## Re: Pass/fail result from data table

Many thanks that works great for 314 but i have tried a couple of others and it is stating fail when it should be pass. Is there something i have missed. I have attached the test sheet again

9. ## Re: Pass/fail result from data table

Your values in Sheet2 for columns D and E are text not numbers

Either change them to numbers
or amend the formula to
=IF(AND(D2>=VLOOKUP(B2,Sheet2!A\$2:E\$29,2,0)+0,D2<=VLOOKUP(B2,Sheet2!A\$2:E\$29,3,0)+0,E2>=VLOOKUP(B2,Sheet2!A\$2:E\$29,4,0)+0,E2<=VLOOKUP(B2,Sheet2!A\$2:E\$29,5,0)+0),"Pass","Fail"

10. ## Re: Pass/fail result from data table

That is brilliant, thank you. Adjusted them to numbers and the original formula worked.
Is it at all possible to get the pass to be green and fail red. I could be asking the impossible but thought i would check

11. ## Re: Pass/fail result from data table

Use Conditional Formatting

Select F2

Conditional Formatting
New Rule
Use a formula to determine...

2 Rules required one for each colour.

=(F2="Pass")
Format as Green

=(F2="Fail")
Format as Red

Use Format painter (paintbrush icon) to copy to other cells

12. ## Re: Pass/fail result from data table

You are a star thank you very much

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1