Help - INDEX returning N/A

[DCVertigo]

Hi,

So rusty, I am building a form to show results from a table using a reference the user enters. For example I have REG as the unique reference to enter as a row (C2 is the header and C3 the start of the data) then show the data from cell under header SERVICE (F2 is the header and F3 the start of that data).

I have tried =INDEX(Table1[#All],MATCH(F14,Table1,0),MATCH(H14,Table1[#Headers],0)) with only #n/a coming up. F14 is the user entered cell for REG and H14 cell with SERVICE as a value.

Can someone help me or point me in the right direction please, many thanks.

[#2]

@DCVertigo You're getting the #N/A error because you're attempting to pass a 2D array (Table1) to the lookup_array parameter in the first MATCH function. This should be a single column only. For example, MATCH(F14, Table1[Column1], 0) will return the row number were the value in cell F14 finds a match in Table1[Column1]. (Note: replace Column1 with the actual column name in your table.)

Furthermore, the array used in the INDEX function should have the same number of rows as the lookup_array used in the first MATCH function, as well as the same number of columns as the lookup_array used in the second MATCH function. As such, you should probably be using Table1 instead of Table1[#All] as the INDEX array.

The correct structure of the entire formula would look something like this:

=INDEX(Table1, MATCH(F14, Table1[Column1], 0), MATCH(H14, Table1[#Headers], 0))

...where the first MATCH function is identifying the row number, and the second MATCH function is identifying the column number.

As an aside, if the XLOOKUP function is available in your version of Excel, the following formula could also be used to produce the same results:

=XLOOKUP(F14, Table1[Column1], XLOOKUP(H14, Table1[#Headers], Table1))