Hi i need to get from table 2 the invoice date from each customer (ref). I tried to use match and index function.

I tried to match the customer reference number in table 1 with the reference number in table 2 so that i can get the invoice date in table 2 but i didnt work out and i get a N/A.

=INDEX(table2!A2:F7624;MATCH(table1!B2;table2!A2:F7624;0);4)

Table 1 enter image description here

Table 2 enter image description here

I tried everything but i always get the error function N/A. Can you help me? Thanks in advance

CodePudding user response：

Since there is not enough unique information provided in the screenshots, and no sample file, here is a possible solution based on the assumption that the invoice date you are looking for is the "fee_amt":

put into table1 cell C2, and drag down:

=INDEX(table2!D$2:D$7624,MATCH(B2,table2!C$2:C$7624,0)*MATCH("fee_amt",table2!E$2:E$7624,0))

CodePudding user response：

I think you are doing it incorrectly. Let's see the context. Index = range of data where you want to search for (Table 1 in your case where you are looking for date).

Match for row = value you are looking for, and the column where the values exist in second table (table 2 only single cell reference number, table 1 whole column of reference number).

Match for column: Sheet 1 name of column header, sheet 2 all the column headers (Table 2 reference of Date header, table 1 whole row which have column header.)
Note 1: Column header names should be same in both sheets.
Note 2: Lock the column in match row and lock the only row number in match column.

=INDEX([Book1]Sheet1!$A$2:$B$31,MATCH($A2,[Book1]Sheet1!$A$2:$A$31,0),MATCH(B$1,[Book1]Sheet1!$A$1:$B$1,0))