I have a spreadsheet and on Sheet 1 in C2 there is a drop down menu with 6 different space options. Then in D2 is a drop down with "Half Day Rate" or "Full Day Rate". Then in Sheet 2 there is a table with 3 column headings, Room, Half Day Rate and Full Day Rate. Under the Room are spaces 1 to 6 and then under each half or full day is a price for each of the spaces. The table array is I5 to I11 and Across to K5 to K11. In E2 on Sheet 1 I need a formula that will put the price in the cell depending on the Space chosen in C2 and whether it is a half or full day.
I have tried this formula but its just leaving the cell blank? Any help please - =IFERROR(IF(AND(C2<>"",D2<>""),IF(D2="Half Day Rate",INDEX(Sheet2!$J$5:$J$11, MATCH(C2, Sheet2!$I$5:$I$11, 0)), IF(D2="Full Day Rate", INDEX(Sheet2!$K$5:$K$11, MATCH(C2, Sheet2!$I$5:$I$11, 0)), "")), ""), "error")
I have tried this formula but its just leaving the cell blank? Any help please - =IFERROR(IF(AND(C2<>"",D2<>""),IF(D2="Half Day Rate",INDEX(Sheet2!$J$5:$J$11, MATCH(C2, Sheet2!$I$5:$I$11, 0)), IF(D2="Full Day Rate", INDEX(Sheet2!$K$5:$K$11, MATCH(C2, Sheet2!$I$5:$I$11, 0)), "")), ""), "error")