Index/Match f(x) which Skips Blanks in Arrays

Try this in B2 of Sheet1:

=IFERROR(VLOOKUP(A2,Sheet2!B:C,2,0),"")

then copy down as required.

Hope this helps.

Pete

Ah, you have just edited your first post while I was composing my reply. Try this instead:

=IF(A2="","",IFERROR(VLOOKUP(A2,Sheet2!B:C,2,0),""))

Hope this helps.

Pete

Hello Pete:

Thank you for your quick response. All I receive in my cell is a blank; in addition, regarding the type of data I am dealing with, the function shouldn't return a blank, i.e., (""), so perhaps an IFERROR or IFNA isn't what I need in this situation. In the past I believe I used a formula where 1 was divided by the same index and match formula. There was an ISNA nested in the INDEX/MATCH formula? What I really need is some way to have index and match skip the blank rows which is generated by my accounting software when it outputs to excel. Best regards.

Did you see my follow-up in Post #3?

Pete

Yes. Upon closer inspection of my input of the formula, I was able to have it return the correct values from the data sheet. Be that as it may, what if I were to have to use an alternative to VLOOKUP due to it's limitation of "looking left" if I remember correctly? Is there an INDEX/MATCH will could achieve the same result? I work with several accounting reports which are output in different formats.

Instead of this formula:

=IF(A2="","",IFERROR(VLOOKUP(A2,Sheet2!B:C,2,0),""))

you could use this INDEX/MATCH equivalent:

=IF(A2="","",IFERROR(INDEX(Sheet2!C:C,MATCH(A2,Sheet2!B:B,0)),""))

Hope this helps.

Pete

P.S. If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete