Index & Match on Blank Cells

[itselflearn]

Hi. Please help on achieving something using Index & Match. Here is the scenario

B1:B15 = DF0067
C1:C10 = blank cells
C11 = XXX
C12:C15 = blank cells


A1= INDEX(C1:C15,MATCH(D1,B1:B15),0
it shows me blank because I think it sees right away DF0067 on B1 with corresponding C1=blank. How do I make it sees C11 value and just ignore blanks? Thank you.

[Harribone]

Insert a new column infornt of B (this will then be column B).
In B1 type:

=IF(D1="","",C1)

you can then use this column to look at and return your value.

[itselflearn]

Sorry Harribone...but your way seems not making sense. And even if I followed it, it will still put me in the same situation.

[benishiryo]

hi there. you didnt mention what's in D1, used in your INDEX formula. i am guessing it's "DF0067". and it couldn't possibly return a blank unless you custom format it. it either returns a value in column C if found, an #N/A if not, & 0 if the 1st match is a blank. you can try:
=INDEX(C1:C15,MATCH(1,INDEX((C1:C15<>"")*(B1:B15=D1),),0))

[itselflearn]

thank you benishiryo..........it worked perfectly. I appreciated your help. Also thank you too to Harribone for trying to figure out to help me, I appreciated it too. Thank you to both of you.

[itselflearn]

one question though that bothers me....what is that means MATCH(1,INDEX((c1:c15<>"")*(B1:B15=D1) in plain english. Thank you.

[benishiryo]

(C1:C15<>"")
this is the 1st logical test. to check if C1:C15 are not blanks. if you select that & press F9 to calculate, you'll get:
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}

(B1:B15=D1)
2nd logical test. to check if B1:B15 is equals to D1. press F9:
{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

TRUEs & FALSEs are equivalent to 1s & 0s. so if you multiply them, you'll get:
{0;0;0;0;0;0;0;0;0;0;1;0;0;0;0}
only when both logical tests are TRUE will it return 1.

the INDEX is a magical way of storing these numbers so that it will not be an array formula. if you remove it & put:
=MATCH(1,(C1:C15<>"")*(B1:B15=D1),0)
it can still work, but you must press CTRL + SHIFT + ENTER as this is now an array formula.

so the MATCH formula looks for "1" inside the array i created {0;0;0;0;0;0;0;0;0;0;1;0;0;0;0} & gives you the position of "1". hope that helps

[itselflearn]

Thank you very much benishiryo. I really appreciated your time explaining and making it clear for me. Thank you so much.