Question

Please show your steps and the formula. i am using office 360

AS2 A B n 1 B D Small Medium Large 10 12 15 30 35 40 25 30 35 2 Sweater 3 Jacket 4 Pants 5 6 Size 7 Medium Price Item Pants 8 9 Required 1: Using a single reference formula, retrieve the price for the item described in Cells AZ & B7. 10 11

Answer 1

To solve the above in excel, the formula to use is INDEX and MATCH. These two formulas are combined to arrive the value of the cell basis the rows and column criteria.

Solution for question 1 as below:



The formula is explained as below:
Refer formula in C7

SUM foc =INDEX(A1:04, MATCH(B7,A2:A4,0), MATCH(A7,B1:01,0)) D E F G H J Medium Large 10 12 15 30 35 40 A B 1 Small 2 Sweater 3 Jacket 4 Pants 5 6 Size Item 7 Medium Pants 8 25 30 35 5 Price (=INDEX(A1:04,MATCH(B7,A2:A4,0), MATCH(A7,81:01,0))| 9

=INDEX(Table array reference (which is A1:D4), Row reference using MATCH function MATCH(Lookup value which is Pants in the rows B7, Range reference which is A2:A4, 0 is selected for Exact match) next is Column reference using MATCH function, MATCH (Lookup value Medium (A7 to search in columns), Column range which is B1:D1, 0 is selcted for exact match) which results in Number 30 which is correct.


C7 =INDEX(A1:04, MATCH(B7, A2:A4,0), MATCH(A7, B1:01,0)) А B E F G H 1 1 Small D Medium Large 10 12 30 35 2 Sweater 3 Jacket 15 40 35 4 Pants 25 30 5 5 6 Size Item 7 Medium Pants Price 30 8 9


Solution for question 2 as below:
For dropdown option in cell G4, you need to go to data validation under Data tab, and select list  under validation criteria and give the range which you want (which is B3 to B7), screenshot as below


Data Validation ? X Settings Input Message Error Alert Validation criteria Allow: List Ignore blank In-cell dropdown Data: between Source: =SB$3:$B$7 Apply these changes to all other cells with the same settings Clear All OK Cancel



G4 Television А B C D E F H 1 Stock ID L458 2 Stock ID Stock Item Price Cost 3 TEL458 Television $ 8,959.00 $884.00 4 LAP5987 Laptop $ 7,840.00 $976.00 5 TAB698 Tablet $7,507.00 $689.00 6 MON632 Monitor $6,690.00 $588.00 7 DRO844 Drone $5,802.00 $555.00 8 Stock Item Television Television Laptop Tablet Monitor Drone


Now for the formula to find stock ID in Celll H4, the below formula is used:

SUM =INDEX(A2:37, MATCH(G4,B2:37,0),1) A B C D E F F G H | J K 1 Stock Item |Tablet Stock ID I =INDEX(A2:B7,MATCH(G4,B2:37,0),1) 2 Stock ID Stock Item Price Cost 3 TEL458 Television $8,959.00 $884.00 4 LAP5987 Laptop $7,840.00 $976.00 5 TAB698 Tablet $7,507.00 $689.00 6 MON632 Monitor $6,690.00 $588.00 7 DRO844 Drone $5,802.00 $555.00 8 9


Explained as for Stock Item "Tablet":
=INDEX(Table array reference (A2 to B7), Now match the row reference using MATCH function, MATCH (Reference value which is Tablet in G4, Range reference is B2 to B7, 0 is selected for exact match and finally the column to refer is the first column A for Stock ID which is 1) which returns the value of TAB698


H4 INDEX(A2:37,MATCH(G4,B2:37,0),1) E F G H A B с D 1 2 Stock ID Stock Item Price Cost 3 TEL458 Television $ 8,959.00 $884.00 4 LAP5987 Laptop $7,840.00 $976.00 5 TAB698 Tablet $ 7,507.00 $689.00 6 MON632 Monitor $6,690.00 $588.00 7 DRO844 Drone $5,802.00 $555.00 8 Stock Item Tablet Stock ID TAB698 9 10


Similarly you can change the stock item in cell G4 using the dropdown and the stock ID would change.