Thread: somewhat complex function needed

I need help figuring out how to do the following. I have 2 spreadsheets. Both have a column with unique numbers in them, let's call it TESTNUM. Both have a second column with a $ value in them I'll call PRICE. I want to create a macro or something that looks to see if TESTNUM from spreadsheet 2 is found in spreadsheet 1. If it is, AND if PRICE 2 is less than PRICE 1 then write PRICE 2 in the PRICE 1 field. Otherwise, don't change anything. I was trying to do it using a vlookup, but that doesn't seem to be robust enough to do some comparison of the fields before deciding which PRICE to use.

Can someone help me? I need this by tomorrow. Thank you very much!!

I'm having some problems getting it to pull the smaller value for some reason. I think we're close but I'll need to work on it some more and make sure I understand the logic of it more thoroughly. I'll let you know if I get it working or not. Thanks for your input.

I'm still having problems. I have attached two sample spreadsheets, all_tests_07022008.xls and subset_tests_07022008.xls. I modified the formula you gave me to fit my spreadsheets. What I'm seeing happen is when it compares the value in column B (when it finds a match for the value of column A in both spreadsheets) no matter if the value in column B of all_tests_07022008.xls is smaller, it still puts in the value from column B of subset_tests_07022008.xls. Can anyone explain why? To me, it looks like this should work as I want it to, but it doesn't. Here's the formula I tried for row 11:

=IF(ISNUMBER(MATCH(A11,[subset_tests_07022008.xls]Sheet1!$A:$A,0)), IF(VLOOKUP(A11,[subset_tests_07022008.xls]Sheet1!$A:$B,2,FALSE)<B11,VLOOKUP(A11,[subset_tests_07022008.xls]Sheet1!$A:$B,2,FALSE),B11),B11)

I would really appreciate any help you could give me. Thank you very much!

I think I figured out my problem. The field type for column B in the all_tests_07022008.xls are not the correct type so apparently were not registering as smaller than the values in column B of subset_tests_07022008.xls. Once I created a new column with the values of column B multiplied by 1 it started working! Whoo hooo!

Thanks for your help!