Need Help
 

I have a workbook containing the following tabs (sheets)
Control
upload
audit
lookup

I am trying to figure out a formula that will do the following
In the audit Tab cell D2 the formula needs to:

compare cell D2 of the control tab to cell D2 of the upload tab
If it matches, lookup the value in column A of the lookup tab and return the
corresponding value from column B of the lookup tab
If it does not match return the word AMEND
If it matches but does not find the value in column A return the word NOTFOUND

I hope someone can help with this one..
--
Regards, Cindy

Need Help
 

Cindy,

Not the easiest set of rules but I 'Think' I followed it

=IF(Control!D2=Upload!D2,IF(COUNTIF(Lookup!A1:A30, Control!D2)=0,"Not
Found",VLOOKUP(Control!D2,Lookup!A1:B30,2,FALSE)), "Amend")

Mike

"Cindy" wrote:

I have a workbook containing the following tabs (sheets)
Control
upload
audit
lookup

I am trying to figure out a formula that will do the following
In the audit Tab cell D2 the formula needs to:

compare cell D2 of the control tab to cell D2 of the upload tab
If it matches, lookup the value in column A of the lookup tab and return the
corresponding value from column B of the lookup tab
If it does not match return the word AMEND
If it matches but does not find the value in column A return the word NOTFOUND

I hope someone can help with this one..
--
Regards, Cindy

Need Help
 

Cindy,
Here is a start
=IF(Control!D2=Upload!D2,VLOOKUP(Control!D2,Lookup !A1:B6,2,FALSE),"AMEND")

But now we need the NOTFOUND
=IF(Control!D2=Upload!D2,IF(ISNA(VLOOKUP(Control!D 2,Lookup!A1:B6,2,FALSE)),"NOTFOUND",VLOOKUP(Contro l!D2,Lookup!A1:B6,2,FALSE)),"AMEND")

Or, if you have Excel 2007
=IF(Control!D2=Upload!D2,IFERROR(VLOOKUP(Control!D 2,Lookup!A1:B6,2,FALSE),"NOTFOUND"),"AMEND")

Of course, you will need to change A1:B6 to suit your data size
best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"Cindy" wrote in message
...
I have a workbook containing the following tabs (sheets)
Control
upload
audit
lookup

I am trying to figure out a formula that will do the following
In the audit Tab cell D2 the formula needs to:

compare cell D2 of the control tab to cell D2 of the upload tab
If it matches, lookup the value in column A of the lookup tab and return
the
corresponding value from column B of the lookup tab
If it does not match return the word AMEND
If it matches but does not find the value in column A return the word
NOTFOUND

I hope someone can help with this one..
--
Regards, Cindy

Need Help
 

Thanks for the help. I was struggling with this one.
--
Regards, Cindy


"Mike H" wrote:

Cindy,

Not the easiest set of rules but I 'Think' I followed it

=IF(Control!D2=Upload!D2,IF(COUNTIF(Lookup!A1:A30, Control!D2)=0,"Not
Found",VLOOKUP(Control!D2,Lookup!A1:B30,2,FALSE)), "Amend")

Mike

"Cindy" wrote:

I have a workbook containing the following tabs (sheets)
Control
upload
audit
lookup

I am trying to figure out a formula that will do the following
In the audit Tab cell D2 the formula needs to:

compare cell D2 of the control tab to cell D2 of the upload tab
If it matches, lookup the value in column A of the lookup tab and return the
corresponding value from column B of the lookup tab
If it does not match return the word AMEND
If it matches but does not find the value in column A return the word NOTFOUND

I hope someone can help with this one..
--
Regards, Cindy

Need Help
 

Thanks for the help I was struggling with this one.
--
Regards, Cindy


"Bernard Liengme" wrote:

Cindy,
Here is a start
=IF(Control!D2=Upload!D2,VLOOKUP(Control!D2,Lookup !A1:B6,2,FALSE),"AMEND")

But now we need the NOTFOUND
=IF(Control!D2=Upload!D2,IF(ISNA(VLOOKUP(Control!D 2,Lookup!A1:B6,2,FALSE)),"NOTFOUND",VLOOKUP(Contro l!D2,Lookup!A1:B6,2,FALSE)),"AMEND")

Or, if you have Excel 2007
=IF(Control!D2=Upload!D2,IFERROR(VLOOKUP(Control!D 2,Lookup!A1:B6,2,FALSE),"NOTFOUND"),"AMEND")

Of course, you will need to change A1:B6 to suit your data size
best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"Cindy" wrote in message
...
I have a workbook containing the following tabs (sheets)
Control
upload
audit
lookup

I am trying to figure out a formula that will do the following
In the audit Tab cell D2 the formula needs to:

compare cell D2 of the control tab to cell D2 of the upload tab
If it matches, lookup the value in column A of the lookup tab and return
the
corresponding value from column B of the lookup tab
If it does not match return the word AMEND
If it matches but does not find the value in column A return the word
NOTFOUND

I hope someone can help with this one..
--
Regards, Cindy

.