| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
