I want to find the value of corresponding clients PO # Invoice # Quote # etc.. & client list is mentioned in different multiple columns.
Formula used : =INDEX(D16,D17,2,MATCH('Client PO & Invoice Details'!A1:ACZ25,'Client PO & Invoice Details'!A1:ACZ1,0))
LOOKING FOR REQUIRED LOOKUP FUNCTIONS WITH MATCH OFFSET INDEX
The staging of your data is really inefficient. If you made a client number column and expanded the data vertically rather than horizontally, you could really save yourself a lot of headaches. For example, if your data was organized that way you could use a pivot tabel to massively simplify this problem.
Barring changes to your data structure, you can do a vlookup for the date against an offset range:
=VLOOKUP($D$17,OFFSET('Client PO & Invoice Details'!$A$1,0,MATCH($D$16,'Client PO & Invoice Details'!$1:$1,0)-1,1048575,6),2,FALSE)
This formula should be placed in D18:D22 below the id and date filters. you will need to increment the vlookup index toward the end of the formula ( ...,2,FALSE) to grab the specific column you are looking for. The 2 will need to be updated to 3,4,5, and 6 in the subsequent formulas to get each of the values.
First The formula finds your client number in row 1, next the offset defines a range 6 columns wide starting at the column that the client number was found in, lastly the vlookup works as usual by finding the date in the first column of the offset range and returning the value of the indexed column.
