If formula question.

[Tofik]

Hi guys, I need some explaining in IF formula. How can I write condition with IF to automate check my Data Base from my table automatically?

something like this: =IF( E3=F3 then past value from G3 in C3 cell) + =If( I already have value in C3 cell but it is not the same as in G3 then change it on right one frome the table and as additional give a red color to me for visually viewing the mistake place in MY Data base).

I need to fully automate C3:C6 column to auto check from MY TABLE by two Column A and B.

Thanks !!!

What I have
MY DATA BASE				MY TABLE
TH	INCH	od		TH	INCH	od
12	3			12	3	14,5
15	4			15	4	2,11
18	39			18	39	31,5
What I need
MY DATA BASE				MY TABLE
TH	INCH	od		TH	INCH	od
12	3	14,5		12	3	14,5
15	4	2,11		15	4	2,11
18	39	31,5		18	39	31,5

 

[Radoslaw Poprawski]

is this it?

Book2
	A	B	C	D	E	F	G
1	What I have
2	MY DATA BASE				MY TABLE
3	TH	INCH	od		TH	INCH	od
4	12	3	14,5		12	3	14,50
5	15	4	2,11		15	4	2,11
6	18	39	31,05		18	39	31,05
7	18	40	#N/A		18	38	18
8
9
10	What I need
11	MY DATA BASE				MY TABLE
12	TH	INCH	od		TH	INCH	od
13	12	3	14,50		12	3	14,50
14	15	4	2,11		15	4	2,11
15	18	39	31,05		18	39	31,05
Sheet1

Cell Formulas
Range	Formula
C4:C7	C4	=VLOOKUP(A4,IF(($E$4:$E$6=A4)*($F$4:$F$6)=B4,$E$4:$G$6,NA()),3,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:C7	Expression	=ISERROR($C3)	text	NO

 

[Tofik]

is this it?

Book2
	A	B	C	D	E	F	G
1	What I have
2	MY DATA BASE				MY TABLE
3	TH	INCH	od		TH	INCH	od
4	12	3	14,5		12	3	14,50
5	15	4	2,11		15	4	2,11
6	18	39	31,05		18	39	31,05
7	18	40	#N/A		18	38	18
8
9
10	What I need
11	MY DATA BASE				MY TABLE
12	TH	INCH	od		TH	INCH	od
13	12	3	14,50		12	3	14,50
14	15	4	2,11		15	4	2,11
15	18	39	31,05		18	39	31,05
Sheet1

Cell Formulas
Range	Formula
C4:C7	C4	=VLOOKUP(A4,IF(($E$4:$E$6=A4)*($F$4:$F$6)=B4,$E$4:$G$6,NA()),3,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:C7	Expression	=ISERROR($C3)	text	NO

I don't Shure by 100% I will try it now and give a comment. Give me 15 minutes to understand the process.

Please Check this. It is my main idea and my main DB.

External Schedule integrate in to Excel

Hallow everyone ! Guys how I can integrate Schedule Standard (STD) in my excel to automatically check 3 cells in my DB ? This is original pdf file of Schedule Standard This is my DB and I marked by color only that places which match with my DB, I want control those cells automatically...

www.mrexcel.com

I search nearly 2 days on YouTube and Google but I cant find an example and that's why I decided to use =IF function and divide work to little steps by =IF formula.
To be honest I don't know which right request I should write on Google because I don't know the Formulas type or name. But now I understood I can do it somehow with VLOOKUP as did it you.
I don't know how to link main DB and Table with 100% right values for autocheking.

 

[Radoslaw Poprawski]

So from what I gathered. you have 2 tables:

1 with expected results and the other with some manual entries that might contain error.

so this:

=VLOOKUP(A4,IF(($E$4:$E$6=A4)*($F$4:$F$6)=B4,$E$4:$G$6,NA()),3,0)

is Vlookup with criteria.
A4 contains what we are searching for, but table_array argument contains if statement:

IF(($E$4:$E$6=A4)*($F$4:$F$6)=B4,$E$4:$G$6,NA())

we are comparing all values from range

$E$4:$E$6

to a value in

A4

Receiving bunch of TRUE and FALSE.
the same is happening here:

($F$4:$F$6=B4)

with more TRUE and FALSE
only when both ranges get a TRUE which is basically equal to 1.we are telling IF to grab a Range

$E$4:$G$6

so we will get only rows (records) that match both criteria, if they don't we get whats in the False argument for if which is

NA()

Function.
Then in col_index of vlookup we state which column we want to retrieve from specified range in the IF statement.

 

[Tofik]

is this it?

Book2
	A	B	C	D	E	F	G
1	What I have
2	MY DATA BASE				MY TABLE
3	TH	INCH	od		TH	INCH	od
4	12	3	14,5		12	3	14,50
5	15	4	2,11		15	4	2,11
6	18	39	31,05		18	39	31,05
7	18	40	#N/A		18	38	18
8
9
10	What I need
11	MY DATA BASE				MY TABLE
12	TH	INCH	od		TH	INCH	od
13	12	3	14,50		12	3	14,50
14	15	4	2,11		15	4	2,11
15	18	39	31,05		18	39	31,05
Sheet1

Cell Formulas
Range	Formula
C4:C7	C4	=VLOOKUP(A4,IF(($E$4:$E$6=A4)*($F$4:$F$6)=B4,$E$4:$G$6,NA()),3,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:C7	Expression	=ISERROR($C3)	text	NO

I try it and understand how I can work with =IF formula. It is good but how I can see mistakes without replace the original value. Is It possible see it *** notes or something like this ?

or I should open the new control column ?
I just want see difference what I had previous and what I have in fact ( from 100% true table ).

 

[Radoslaw Poprawski]

you can do it however you want.
if you apply my solution ull get a value from specified column as explained. now yu can do simple comparison to get TRUE / FALSE
just add = at the end of my formula and compare it to control cell

=VLOOKUP(A4,IF(($E$4:$E$6=A4)*($F$4:$F$6)=B4,$E$4:$G$6,NA()),3,0)=ControlCell.

you can also subtract:

=VLOOKUP(A4,IF(($E$4:$E$6=A4)*($F$4:$F$6)=B4,$E$4:$G$6,NA()),3,0)-ControlCell

if you don't want to see NA error but some text:
then change NA() to "SomeText"

for any case ull need additional column to put vlookup in
you can also put as many criteria as you want
(CriteriaComparison1)*(CriteriaComparison2)*CriteriaComparison3)*...*(CriteriaComparisonN)

@Tofik

Are you from Poland by any chance?