Advance lookup formular (simplification needed)

[pabloponza]

Dear all

I need a advance lookup function that can return only value, skip the blank, then return value (if any more after blank) right next to the first returned value. I want this loop to continue until the end of the source table. I had write a formular but i think it is too unnecessary complex. can anyone help me simplified it?

Thanks a lot!!

classify formular2.xlsx

[Tony Valko]

Try this...

This array formula** entered on Sheet1 in cell C2:

=IFERROR(INDEX(Sheet2!2:2,SMALL(IF(Sheet2!$C2:$I2<>"",COLUMN(Sheet2!$C2:$I2)),COLUMNS($C2:C2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down to C7 then across until you get a column full of blanks.

[benishiryo]

hi pabloponza. i noticed you ignored the Division in Column C of Sheet2, so i did the same. here's an alternative non-array solution:

Please Login or Register  to view this content.

change the one in red to "" if you want to show as blanks instead of 0

[pabloponza]

Wowwww! you guys are amazing!!
Thank you very much Tony Valko and Benishiryo
Both formular work perfectly

If you have time, can you explain about how the formular work? I would be very thankful

Cheers

[Tony Valko]

I don't have enough time to do it tonight. I'll try to write up an expalantion tomorrow if I get a chance (I already know I'll be pretty busy tomorrow).

[macov_vanbasten]

very useful topic..

Thanks all for your contribution

[benishiryo]

let me try explain both our formulas. do chip in if i missed something, Tony.

Tony's Formula without the IFERROR for C3
INDEX(Sheet2!2:2,SMALL(IF(Sheet2!$C2:$I2<>"",COLUMN(Sheet2!$C2:$I2)),COLUMNS($C2:C2)))

Sheet2!2:2
By putting the the array of the INDEX formula as "Sheet2!2:2", it's saying that our results we want is in row 2 of Sheet2.

IF(Sheet2!$C2:$I2<>"",COLUMN(Sheet2!$C2:$I2))
What we need now is the Column number it should return from the whole of row 2. Tony then uses the IF statement to say if C2:I2 in Sheet2 is blank, return the column number of it. COLUMN(C2) will return 3, COLUMN (D2) will return 4, and so on. if you select the above portion of the formula & press F9 to calculate, you'll notice the following results:
{3,FALSE,5,FALSE,FALSE,8,FALSE}
This means column 3, 5, 8 are not blank.

SMALL(IF(Sheet2!$C2:$I2<>"",COLUMN(Sheet2!$C2:$I2)),COLUMNS($C2:C2))
in C3, what we want is the 1st blank (i.e. column 3), then in D3, the 2nd blank (i.e. column 5). hence, we can use the SMALL formula to say we want the 1st smallest number, then in D3, the 2nd smallest, & so on. to make the number increase by 1st, 2nd, 3rd; we use "COLUMNS($C2:C2)". notice an "S" behind. unlike COLUMN, COLUMNS return the total number of columns you selected. so selecting $C2:C2 is only 1 column in total & will return 1. since we fixed the column in the 1st part, D3 will read $C2:D2 & that's 2 columns; returning 2.

The IFERROR simply returns something else you want to see (blank or zero) if the above formula hits an error. it hits an error when you want to return the 5th value for row 2 when there are only 4 values without blanks

My formula without the IFERROR for C3
INDEX(Sheet2!$D2:$I2,SMALL(INDEX((Sheet2!$D2:$I2<>"")*(COLUMN(Sheet2!$D2:$I2)-COLUMN(Sheet2!$D2)+1),),COUNTIF(Sheet2!$B2:$I2,"")+COLUMNS($C2:C2)))

it actually has the same concept as Tony's but avoids using the array formula.

INDEX((Sheet2!$D2:$I2<>"")*(COLUMN(Sheet2!$D2:$I2)-COLUMN(Sheet2!$D2)+1),)
instead of using IF, i stored the values inside the INDEX formula by doing a multiplication of those with non-blanks * the column number. what i would get would be a result of whether D2:I2 are blanks:
{FALSE,TRUE,FALSE,FALSE,TRUE,FALSE}
multiplied by column numbers:
{1,2,3,4,5,6}
FALSE in Excel is regarded as 0s & TRUEs as 1s. so when multiplied, they return:
{0,2,0,0,5,0}
unlike Tony's which has array of the whole of row 2, i only selected D2:I2. and i ignored column C of Sheet2 because you seemed to have omit that. hence, my column numbers & number of non-blanks are different from Tony's.

COUNTIF(Sheet2!$B2:$I2,"")+COLUMNS($C2:C2)
and since my values you saw above contains zeroes, i cannot simply use COLUMNS to get the 1st, 2nd smallest values like Tony. my 1st & 2nd values are 0. i used COUNTIF to count how many zeroes i have & then add the COLUMNS formula. so i have 4 zeroes. and adding the COLUMNS formula, what i need is the 5th smallest value from above.

hope that helps

[pabloponza]

benishiryo, Thank you so much!
Thank you for your time and kindness to explain both formula. It really helps.