Working with data from two sheets

[jumbel]

I have the following workbook. I would like to display the values in the column 'solution' for each row in sheet1 depending on the condition intrepeted from sheet2.

for example, consider the first row when the grade is 17.5 and the fact is 129, it refers to the 8th row in sheet2, so the solution B6 should be displayed.

I dont want to write the logic in if loops as the values in 'fact'column will be changed regularly.

does anybody has a better approach to this one?
Got stuck since 3 days.Please help!!

[Adonaioc]

I have throw together an access database the builds a table based on matching the range to the "fact". i edited some of your ranges to match the format so they may not be correct, let me know if that is what you are looking for.

[Adonaioc]

here it is

[jumbel]

I see that the records are decreased to 301. All the records in the file are unique, I just chnged the numbers to 1,2,3 so that it would be easy to understand for you.


Looks good, but I still didnt get the concept of how you are getting the solutions. Can you please PM me more on this??

[Adonaioc]

I believe the problem is that the grade-fact combos are not unique and the relationship needs to be edited

[RonMcK]

jumbel,

How should I (or a program) distinguish between assigning Solution = B1 and Solution = B2 when the Grade = 16 and the 'fact value' <121?

Similar questions are the other highlighted pairs in the screen capture of your Sheet2.

I would suggest that you load your Rule Table (sheet2) into an array and then test each Grade/Fact Value pair against the array to determine the Soltion and Optional solution, if appropriate. This will allow you to change the table entries, lncluding adding and/or deleting rows from the table, without your needng to change the program each time.


Thanks,