Use Index Match with an ISBLANK?

[triggfamily5]

Below is my current formula that I need to tweak, and not sure the best way to do it.

If column 11 is blank, I want it to give me column 10 instead.

=IFERROR(INDEX('PowerPlant Query'!$A:$R,MATCH('Pivot Data'!$E10,'PowerPlant Query'!$B:$B,0),11)," ")

Thanks for any help you can provide.

[ben_hensel]

Maybe something like:

Please Login or Register  to view this content.

[triggfamily5]

That worked Perfectly! Thank you so much...

Now I can play with that and try and figure out how to add EST to the front of it when i utilized column J...

You are awesome, thank you..

[iwanradius]

Hi, a further question.

What if I want the INDEX MATCH formula to just search the result cell or above until it finds non-blank cell (not just a cell above the result cell).

Thanks

Iwan

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

[iwanradius]

Hi Glenn,

Im sorry for that. I'll attach a dummy file.

In short, I wanted the INDEX formula to return value from the 'Item Code'.
If the result row is empty (since there can be only be one unique 'Item Code'), then, I want it to get the value from the row above it
until it finds one that is non-blank.



Thanks for your interest in trying to answer the question.

[Glenn Kennedy]

I suspect your real problem is more complicated that this, but...


=IFERROR(LOOKUP(2,1/($B$4:B4<>""),$B$4:B4),"")

[Glenn Kennedy]

Iwan, I have just spotted that you are not the original poster. If my answer is incorrect, please start your OWN thread. Thread "Hijacking" is not allowed here, as it can cause serious confusion if two people's problems are being addressed in one thread!!

[iwanradius]

Oh sorry about that. I found this thread when researching about my problem which I thought is quite similar. Hence, I asked it here.

I'll try your suggestion, and if it doesn't work, then I will start a new thread.


Thanks Glen for the insight.


Iwan