Advance excel formula

Question

I want answer of that question

I want answer for that question because I was tried many times. In this question they want the value for product in each quarter with the help of each direction. The directions are east west north south.

Answer 1

=WRAPCOLS(XLOOKUP(I10,A3:A8,B3:Q8),4)

Answer 2

For any NON-Office 365 users out there, this can also be achieved with INDEX/MATCH, using the secondary syntax for INDEX (reference, row_num, [column_num], [area_num]).

Build the reference parameter by selecting each area of the data range individually for East, West, North and South, separated by commas and enclosed in brackets:

=INDEX((C3:F8,G3:J8,K3:N8,O3:R8), row_num, [column_num], [area_num])

To solve for the row_num, [column_num], and [area_num] parameters, use the MATCH function:

=INDEX((C3:F8,G3:J8,K3:N8,O3:R8), MATCH(I10,B3:B8,0), MATCH(G13:G16,C2:F2,0), MATCH(H12:K12,H12:K12,0))

Since the lookup values for [column_num] and [area_num] are in the same order as the source table, array constants can be used to simplify the formula:

=INDEX((C3:F8,G3:J8,K3:N8,O3:R8), MATCH(I10,B3:B8,0), {1;2;3;4}, {1,2,3,4})

To make this work in older versions of Excel, first select the entire output range H13:K16, type the formula and press Ctrl+Shift+Enter.