First time posting here, but it really seems like some experts hang around, so I appreciate any help I can get.
I have a list of numbers that I want to cross reference against another list (database column below), and return values in adjacent cells. Problem is that the query doesn't need to be an exact match, but within some range. In the example I am using value +- (0.005/100 * value). An easy way to think of this is two conditions must be met:
1) (Positive Match - Query ) < ( (0.005/100) * Query)
2) (Positive Match - Query ) > (-1 * (0.005/100) * Query)
<tbody>
</tbody>
The condition columns are here for illustration. When I actually implement this, there will be a column of queries and return values adjacent to values that satisfy query (there should only be one value to satisfy both conditions, so multiple matches aren't an issue).
I assume there is some way to have a nested if statement within an index/match function, but I can't seem to figure it out. I don't think an array would be used here, but any suggestions are welcome.
Thank you for the help.
I have a list of numbers that I want to cross reference against another list (database column below), and return values in adjacent cells. Problem is that the query doesn't need to be an exact match, but within some range. In the example I am using value +- (0.005/100 * value). An easy way to think of this is two conditions must be met:
1) (Positive Match - Query ) < ( (0.005/100) * Query)
2) (Positive Match - Query ) > (-1 * (0.005/100) * Query)
| Query | Return Value | Database | Value | cont'n 1 | condt'n 2 |
| 520.3 | 17N | 519.5 | 18N | True | False |
| 520.32 | 17N | True | True | ||
| 521.5 | 19M | False | True |
<tbody>
</tbody>
The condition columns are here for illustration. When I actually implement this, there will be a column of queries and return values adjacent to values that satisfy query (there should only be one value to satisfy both conditions, so multiple matches aren't an issue).
I assume there is some way to have a nested if statement within an index/match function, but I can't seem to figure it out. I don't think an array would be used here, but any suggestions are welcome.
Thank you for the help.
