Hi,
I am currently working on returning multiple values from large excel data base. The headers of data from A1 to J1 -
Figure- Gross - Net - Exchange -Amount - Transaction id - Ref. id- Recipient - Date - Type
And all these containing the data upto 400 rows. The header FIGURE contains data like (A2-A7)
470-480-480-590-620-620 .
Now the problem is that i have a list of FIGURE which i need to fetch from the above available data. like 470, 480 , 590 , 620. So i updated the formula like that
Look up value (M1) 480
Figure (N1) =IFERROR(INDEX($A$2:$J$62,SMALL(IF($A$2:$A$62=$M$2,ROW($A$2:$A$62)-1,""),ROW(A1)),MATCH(N$1,$A$1:$J$1,0)),"") and it works perfectly fine in row and column dragging without any issue. I want to make some changes in above formula so that when it would find the values for 480 then it automatically fetch the details for value 590, LOOK FOR 620 and so on. If i would use referencing like that M2 (WITHOUT DOLLAR ) then values across the cells to N1 O1 P1 would not work . Because 480 has two results so $M2 , M$2 would not work in either row or column. So when it'll find the details of M1 (480) and place the value in N1 to P1, then N2 to P2. After that it would pick 590 and use the above syntax.
If you need further info please ask freely .
Thanks
I am currently working on returning multiple values from large excel data base. The headers of data from A1 to J1 -
Figure- Gross - Net - Exchange -Amount - Transaction id - Ref. id- Recipient - Date - Type
And all these containing the data upto 400 rows. The header FIGURE contains data like (A2-A7)
470-480-480-590-620-620 .
Now the problem is that i have a list of FIGURE which i need to fetch from the above available data. like 470, 480 , 590 , 620. So i updated the formula like that
Look up value (M1) 480
Figure (N1) =IFERROR(INDEX($A$2:$J$62,SMALL(IF($A$2:$A$62=$M$2,ROW($A$2:$A$62)-1,""),ROW(A1)),MATCH(N$1,$A$1:$J$1,0)),"") and it works perfectly fine in row and column dragging without any issue. I want to make some changes in above formula so that when it would find the values for 480 then it automatically fetch the details for value 590, LOOK FOR 620 and so on. If i would use referencing like that M2 (WITHOUT DOLLAR ) then values across the cells to N1 O1 P1 would not work . Because 480 has two results so $M2 , M$2 would not work in either row or column. So when it'll find the details of M1 (480) and place the value in N1 to P1, then N2 to P2. After that it would pick 590 and use the above syntax.
If you need further info please ask freely .
Thanks
