Snasui Excel Forum

Hi
Dear
I have 5 columns like these
CHARGE CODE 1 CHARGE AMOUNT1 CHARGE CODE 2 CHARGE AMOUNT2 CHARGE CODE 3 CHARGE AMOUNT3
In Column A it should contain 2 ,C contain 7 and E contain 8 but the system is not fixed that columns.in column A it contains 2 and 7 ,C contain contain 8 but it is not like that.
How can I return value by row.i ask for IF function.vlookup won't work well because it returns only first value.
For example
J4 is 2 so from J5 it should return value from column B
K4 is 7 so from K5 should return value ajacent to it.
L4 is 8 from L5 should return value from adjacent to it
and so on

I attached an Excel

Best wishes

Try this formula,

Cell J5

=IFERROR(INDEX($B:$F,AGGREGATE(15,6,ROW($A$5:$A$41)/(INDEX($A$5:$E$41,0,2*(COLUMNS($J:J)-1)+1)=J$4),ROWS(J$5:J5)),2*(COLUMNS($J:J)-1)+1),"")

Enter > Copy to right then down

Thanks it works fine now

but after checking a bit more I check code 7 and 8 lose if it comes in column A for 7 and 8 in column C.is there any workaround this?
row 22,33,35

J5 =IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(10000*ROW($A$5:$A$41)+COLUMN($A4:$F4)+1)/($A$5:$E$41=J$4)/ISODD(COLUMN($A4:$F4)-COLUMN($A4)+1),ROWS(J$5:J5)),"r0c0000"),0),"")

Hope this help.

Thanks