Help with VLOOKUP, maybe?

[jrh2558]

Greetings all,

I'm a novice when it comes to excel, however, I have some programming background so I'd like to think I am able to understand this with some help.

What I'm trying to accomplish:

Basically I have 7 variables that change per part number that I'd like to cross reference when quality control inputs after checking dimensions of product. So what I am trying to create is a way to input my nominal dimension, for example, 0.98, 1.15, 0.58 with a standardized tolerance of +/- .100mm. So essentially when quality control checks the part, they input their number and returns in the next cell over a PASS or FAIL or a fancy green/red cell...

0.98	0.90	PASS
1.15	1.20	PASS
0.58	0.70	FAIL

Something similar to this... Like I said I'm a novice, so I don't really even know how to search for something like this to find an example...

Any help or direction on where to learn more about this would be greatly appreciated.

Best Regards,
Jay

[Tony Valko]

One way...

A2 = 0.98
B2 = 0.90

=IF(A2=MEDIAN(A2,B2+0.1,B2-0.1),"Pass","Fail")

[Fett2oo5]

Heh, just had a convo about something very similar.
Take a look at this, it might be what you are looking for.

Select the cell you want to place conditional formatting to.
Under Format menu select Conditional Formatting:

Cell Value is equal to 0 = blank
Cell Value is between $C3 and $D3 = Green
Cell Value is not between $C3 and $D3 = Red

On the example I've placed this Cond Form on the ten quality check cells for each measurement. When a measurement value is entered the cell will change color, but remain blank (white) until a value is entered.

[jrh2558]

Heh, just had a convo about something very similar.
Take a look at this, it might be what you are looking for.

Select the cell you want to place conditional formatting to.
Under Format menu select Conditional Formatting:

Cell Value is equal to 0 = blank
Cell Value is between $C3 and $D3 = Green
Cell Value is not between $C3 and $D3 = Red

On the example I've placed this Cond Form on the ten quality check cells for each measurement. When a measurement value is entered the cell will change color, but remain blank (white) until a value is entered.

Thanks for the quick reply!

This is pretty close to what I am looking for and think I can get this to work but is there some sort of OR statement I can use? Where my pass/fail would appear based on three distinct dims off three different points measured on ONE part?

Ao = 1.50 +/- .10
Bo = 2.00 +/- .10

and if either of those numbers are out of spec it throws up one pass/fail on a single row. Like in your example I'd want to have 36, 6, AND 27 checked to where if ANY of those dims were out, it'd throw a fail.
When I attempted to just wrap more dims in your example it gave some #VALUE error...

Also, I should mention I'm trying to create this in a portrait style.

EDIT: I think I figured out how to use the AND, however, it still is giving me a #VALUE! error, I attached my worksheet, maybe someone could take a quick look and see what I need to fix?




Best Regards,
Jay

[Fett2oo5]

I didn't see your attachment before I began, sorry. Attached is what I worked up so far. To add a more dimensions select the bottom row of the table and expand it down, all the formulas and conditional formatting will continue.

You can then enter in the dimensions you need, you can also set the tolerance to what you need as well.


I'll see what I can do with your example.

[Fett2oo5]

Alright, took some redoing and organizing... Is this what you are looking for?


It's getting kind of late here, I'm going to head home, eat and crash. I'll check back in with you tomorrow morning. G'night

---

Click the star in the bottom left if the suggestion helps you!
If your problem is solved, then please mark the thread as SOLVED:
Go to above your first post → Thread Tools → Mark your thread as Solved

[jrh2558]

Oh wow, thank you very much for helping me with this, your last attachment really helped me understand more how the functions are used.. I think I could definitely utilize this inside of my example with some tweaks..

Any additional help is way more then I expected to get..

Best Regards,
Jay

[jrh2558]

Best Regards,
Jay

[Fett2oo5]

You are welcome, I'm glad I was able to help. If you need any more help with it just let us know.

And welcome to the forums.

---

Click *, if the suggestion helps you!
If your problem is solved, then please mark the thread as SOLVED:
Go to above your first post → Thread Tools → Mark your thread as Solved

[jrh2558]

You are welcome, I'm glad I was able to help. If you need any more help with it just let us know.

And welcome to the forums.

---

Click *, if the suggestion helps you!
If your problem is solved, then please mark the thread as SOLVED:
Go to above your first post → Thread Tools → Mark your thread as Solved

Back again....... So I tried to add in the rest of my variables myself however, after I get past the row you helped me with, it doesn't recognize MY inputs to PASS/FAIL.. I manually entered it in for the 14th row, but can't seem to figure out how to spread it down the rest of the way... Could you help once more with this?

Best Regards,
Jay Robert

[jrh2558]

I actually figured out the rest of what I was trying to accomplish. Here's the final template for anyone looking for a similar document.

Best Regards,
Jay