200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
------------------------
Imagine an allowance is given to occupants based on the number of rooms they occupy
1. The same occupant can be have 0 or 1 to 4 rooms but will different allowance?
2.What formula will I used to extract each room allowance from the table so that each occupant total will equal the total?
3.Please assist to help with the right formula to pull the allowance amount from the table into the rows for each room number?
Please try this formula.
=VLOOKUP($B3,$G$3:$L$12,$C3+2,FALSE)Observe that the lookup column is determined by C3 and the range $G$3:$L$12. Since zero rooms is in the second column of the range the formula to calculate the correct column = C3+2.
An error will result if someone enters a number larger than 4 in column C. You might catch that error and display some message. However, in effect, the error message displayed by the system should be desirable in such a case so that no extra accommodation is needed unless you have a maximum. In that case you can embed the above formula in an IFERROR function.
=IFERROR(VLOOKUP($B3,$G$3:$L$12,$C3+2,FALSE),100)This version of the formula will charge an allowance of 100 if the number of rooms is greater than 4.
Isaac
If you want to use INDEX/MATCH (as you attempted) then the formula for cell D3 (e.g.) should be:
=INDEX($H$3:$L$12,MATCH(B3,$G$3:$G$12,0),C3+1)Wrap a "catch-all" IFERROR function around it (as Variatus suggests) if you like.
Also, kindly edit your question title- it's very deceptive for other users since it has nothing to do with UDFs. Something like "Looking up values from range" might be more appropriate.