I am trying to find a way to look up min/max pay from data sheet if it matches the job code & age in the in the input sheet. I tried index & match function but it didn't work for me.
INPUT SHEET DATA SHEET
<colgroup><col span="2"><col><col span="9"><col></colgroup><tbody>
</tbody>
- If I give input as B456 & age 25 then output should be 50,100 & not valid state.
If the age is 81 then output will be doesn't exist..
Please find attached sample data
INPUT SHEET DATA SHEET
Input | Age | 25 | Job code | Min age | max age | min pay | max pay | State | ||||
Input | State | IL | A123 | 10 | 50 | 100 | 100 | TX,NY,IL,MN | ||||
Input | Job Code | B456 | B456 | 20 | 30 | 50 | 100 | NY,NJ | ||||
Output | Min Pay | 50 | B456 | 31 | 60 | 100 | 200 | NY,NJ | ||||
Output | Max pay | 100 | B456 | 61 | 70 | 150 | 200 | NY,NJ | ||||
Output | Valid | Not valid state | B456 | 71 | 80 | 170 | 201 | NY,NJ | ||||
C123 | 20 | 60 | 100 | 150 | AR,TN | |||||||
D8910 | 30 | 90 | 40 | 200 | AP,KY | |||||||
<colgroup><col span="2"><col><col span="9"><col></colgroup><tbody>
</tbody>