Is there a formula that allows me to determine if a row in a given range or table exists with specified values in two different colums regardless of sort orders? I have a two ranges with data concerning payments. The first range records liabilities and the second range records payments made. Both ranges contain many columns, two of which are Due Date (the payment due date) and Amount (payment amount). Only full payments are allowed so the Amount columns in both ranges will always contain matching values. In the Liabilities range, I want to add a column that displays an "X" if the liability shows as paid in the Payments range. Multiple rows could contain the same Due Date but the Amount values will be unique for any given Due Date (i.e., there will never be multiple rows with the same Due Date and Amount). Multiple rows could contain the same Amount but the Due Dates would always be different. The rows in the Liabilities range will NOT be sorted by Due Date or by Amount. The rows in the Payments range are sorted by Due Date but there is no secondary sort on Amount. The formula VLOOKUP(ROUND(E4,2),$L$4:$M$60,2,FALSE)=F4 (where column E is the Liabilities Amount, F is the Liabilities Due Date, L is the Payment Amount, M is the Payment Due Date) looks up the Liabilities Amount in the Payments range, returns the Payment Due Date and returns True if that matches the Liabilties Due Date. However, this will fail for the second of two identical Amounts with different Due Dates because VLOOKUP will match the first Amount and return the first Due Date, which will not match the second Due Date.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Lookup based on multiple column matches
- Thread starter mbailey5
- Start date
Similar threads
- Solved
- Locked
- Question
- Question
