h3rm1t9536
asked on
Help needed on using IF formula to check colums of data on seperate sheets and return a value if true
Hi All,
Hopping someone can help here.
I have a spread sheet that contains 3 sheets;
Sheet 1 - Contains values from a Database and their corresponding database ID in 2 columns
Sheet 2 - Contains values exported from another Database
Sheet 3 - Ideally will contain the database ID from sheet 1 if the data is processed in the IF function correctly
I initially built 3 sheets to mirror what I need to do and tested it with dummy data and it worked fine - when I now put in the real data the formula is throwing up a mixture of #value errors and false values which I know should be returning a Database ID number instead.
If the method we are trying to apply to normalise the data into sheet 3 works we would need to apply it to a data set in sheet 2 that will contain approx. 1850 records.
The formula we are using is:
=IF('Database Tables'!$U$4:$U$102='Zips export master'!$G$4:$G$102,'Datab
What I need it to do is check 'Database Tables' and 'Zips export master' if it finds data in both sheets it should return the Database ID number next to the product match in 'Database Tables'
As I said in the test tables and formula it worked as expected and we are not sure why it is now not working as expected. We have tried formatting all data as text/general/custom,text etc and nothing makes a difference.
If anyone can shed any light on why this isn't working we would be very grateful.
I am aware there will be better ways of doing this by coding a custom upload function on the target DB but we need to explore this first as this is the cheap option!
File with test tables and live data attached.
Many Thanks
Hopping someone can help here.
I have a spread sheet that contains 3 sheets;
Sheet 1 - Contains values from a Database and their corresponding database ID in 2 columns
Sheet 2 - Contains values exported from another Database
Sheet 3 - Ideally will contain the database ID from sheet 1 if the data is processed in the IF function correctly
I initially built 3 sheets to mirror what I need to do and tested it with dummy data and it worked fine - when I now put in the real data the formula is throwing up a mixture of #value errors and false values which I know should be returning a Database ID number instead.
If the method we are trying to apply to normalise the data into sheet 3 works we would need to apply it to a data set in sheet 2 that will contain approx. 1850 records.
The formula we are using is:
=IF('Database Tables'!$U$4:$U$102='Zips export master'!$G$4:$G$102,'Datab
What I need it to do is check 'Database Tables' and 'Zips export master' if it finds data in both sheets it should return the Database ID number next to the product match in 'Database Tables'
As I said in the test tables and formula it worked as expected and we are not sure why it is now not working as expected. We have tried formatting all data as text/general/custom,text etc and nothing makes a difference.
If anyone can shed any light on why this isn't working we would be very grateful.
I am aware there will be better ways of doing this by coding a custom upload function on the target DB but we need to explore this first as this is the cheap option!
File with test tables and live data attached.
Many Thanks
NBVC
I don't see the attachments...
ASKER
Apologies NB_VC - Hopefully they attach here:
ISD-Import-Template.xlsx
ISD-Import-Template.xlsx
Ok, so are you comparing direct rows to each other, ie. you are checking ('Database Tables'!$U$4 = 'Zips export master'!$G$4, then row 5, row 6, etc... or are you simply checking that each exists in both sheets? Kind of confusing...
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
I need the formula to look at both columns of data and if it finds data in the Zips export master sheet and in the defined range on the Database tables sheet it would return the ID number that is next to the matching data entry in the Database tables sheet.
Does that make sense?
Does that make sense?
ASKER
Ok let me apply that and see where I get to
ASKER
OK that is working in G3 exactly as needed but in K3 it is returning TRUE into the cell where I need it to return the ID - for example in K3 the returned value should 12 (for Public Awareness).
Are you sure, this formula did, indeed return 12 for me:
=IFERROR(INDEX('Database Tables'!$Z$4:$Z$22,MATCH('
make sure you didn't enter the formula into the same cell twice ;)
=IFERROR(INDEX('Database Tables'!$Z$4:$Z$22,MATCH('
make sure you didn't enter the formula into the same cell twice ;)
ASKER
Hi,
Thanks for your help on this much appreciated.
I have copied the formula again and I am still getting values of TRUE returned.
Have attached the sheet again with your new formulas in place.
Thanks
ISD-Import-Template-updated.xlsx
Thanks for your help on this much appreciated.
I have copied the formula again and I am still getting values of TRUE returned.
Have attached the sheet again with your new formulas in place.
Thanks
ISD-Import-Template-updated.xlsx
ASKER
Hi Again,
Not sure why but have deleted all from that column and re-input the formula and it is all working now.
Thank you so much for you help
Not sure why but have deleted all from that column and re-input the formula and it is all working now.
Thank you so much for you help
It seems as though it is as I had implied... you had the same formula twice in the cell, therefore it was comparing results and returning TRUE since you would always get the same result on either side...