Need Help

[Cindy]

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..

 

[Mike H]

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

 

[Bernard Liengme]

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!D2,Lookup!A1:B6,2,FALSE)),"NOTFOUND",VLOOKUP(Control!D2,Lookup!A1:B6,2,FALSE)),"AMEND")

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

Of course, you will need to change A1:B6 to suit your data size
best wishes

 

[Cindy]

Thanks for the help. I was struggling with this one.

 

[Cindy]

Thanks for the help I was struggling with this one.