How to obtain validation list values from 3 columns based on INDEX MATCH of value offset from cell
Requirement:
The user is having some trouble creating a Data Validation List formula that works.
The user has the following Price list table:
| Item | LIST | P1 | P2 | P3 |
| Item1 | $5.00 | $4.00 | $7.00 | $2.00 |
| Item2 | $6.00 | $5.00 | $3.00 | $5.00 |
| Item3 | $4.00 | $3.00 | $5.00 | $7.00 |
| Item4 | $5.00 | $5.00 | $2.00 | $3.00 |
On the Main sheet the user has a table as follows:
| Item | LIST | Price |
| Item1 | $5.00 | DROPDOWN |
| Item3 | $4.00 | DROPDOWN |
What the user is trying to do is on the main sheet, based on the selection on the item column, the user needs the 3 prices (P1, P2, P3) to be in a dropdown in the "Price" column.
Solution:
=INDEX(INDIRECT("price_list[[P1]:[P3]]"),MATCH(A2,INDIRECT("price_list[PN]"),0),0)
Obtained from the OzGrid Help Forum.
Solution provided by NBVC.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use Index/Countif based on multiple factors including range |
| How to use SUMPRODUCT with INDEX MATCH multiple criteria |
| How to create VBA for index and match based on sheet criteria |
| How to use VBA code to find colour index which is not found on excel colour palette |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
