Suppose I have similiar table [over 200 rows and over 80 columns], but with B column as additional description of "vendorname", and with others (column C and rightwards) filled with formulas returning either TEXT or "".

You wrote: "=IFERROR(INDEX(...),"") shows empty cell after displaying all data".

What, if I'd like formula to continue returning texts from the next rows (vendorname_1, vendorname_2 and so on)?

And I can't imagine, how to use MATCH in such a case.

I think of a formula that "knows": 'OK, there's no text in this row (vendorname_1) anymore, let's offset one down (vendorname_2) and egzamine it'.

I'd like to end up with three-column wide table, where in first two columns appropriate "vendorname" and "description of vendorname" are repeated as long, as there are text values (data) being returned by formula in the third for that particular "vendorname"...

Any help would be greatly appreciated! (And sorry for my english.) Best regards.

my table

  • Who is "you" in "you wrote"? What is the formula you are using? Post a data sample or a screenshot of your data and explain the desired result. – teylyn Jan 29 '17 at 23:10
  • It was my first post and I thought I'm asking Máté Juhász, who answered question by other user – Radian Jan 29 '17 at 23:33
  • see: superuser.com/questions/990125/… – Radian Jan 29 '17 at 23:33
  • I've attached screenshot of my table - link is on very end of my post (i.stack.imgur.com/O5XtV.gif) – Radian Jan 29 '17 at 23:36
  • Your description does not make much sense. There are no vendors in your screenshot. It's a bit presumptuous from you to expect us to root around in an old question to find the formula you are referring to. – teylyn Jan 30 '17 at 0:06

If you want to achieve what your screenshot shows, you'd do better without formulas. What you need is to unpivot your table.

There are ways to do that with Excel. Either use a reverse pivot or the Unpivot command in Power Query for Excel 2010 and 2013, also called Get and Transform in Excel 2016.

| improve this answer | |
  • Thank you a lot @teylyn! In fact, it was my second encounter with Power Query Editor (the first being only yesterday) - but I managed to achieve my goal! Nevertheless, I'm still curious if this is possible with formula... – Radian Jan 30 '17 at 1:28
  • Not really. You would have to roll your own with VBA. – teylyn Jan 30 '17 at 1:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.