Display contents of first occupied cell to the right - VLOOKUP?

[vfr700f2]

Hi y'all!

In each row of my sheet, at least one cell in Columns B thru J contains data. I want the data from whichever is the first cell that is not blank to appear in column A. I think VLOOKUP is what I need, but I don't quite know how to make that work.

Example: B1 is blank, C1 is blank, D1 has data, E1 has data. I want the data in D1 to appear in A1. Next row, B2 has data, C2 is blank, D2 has data, E2 is blank. I want the data from B2 to appear in B1.

All data is text and ends in "WK".

Is VLOOKUP the right thing to use? How do I need to write it?

Thanks!!!

kevin

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E	F
1	smip			smip		jurg
2	glorp		glorp
3	plib			plib	blib
4	cronk				cronk
5	------	------	------	------	------	------

Entered in A1 and copied down:

=INDEX(B1:F1,MATCH("*",B1:F1,0))

[JohnTopley]

Try

=INDEX($B1:$J1,MATCH("*",$B1:$J1,0))

[vfr700f2]

Using that formula, if B has data, it appears in A. If B is blank, A is blank. It's not continuing to look to the right until it finds something.

[JohnTopley]

No it looks until finds first non-blank cell.

See response #2 from Tony which shows the results you get.

[Tony Valko]

That makes it sound like the cells may have formulas and some of them return formula blanks "".

Try this version:

=INDEX(B1:F1,MATCH("?*",B1:F1,0))

[vfr700f2]

My "blank" cells do seem to have something in them. I don't know why. My sheet is exported from SAP and I have to hit delete or clear contents to make things work right.

But by using =INDEX(J2:P2,MATCH("*WK",J2:P2,0)) I got it to ignore whatever is in my "blank" cells and now it works.

Thanks guys!!

kevin

[Tony Valko]

Good deal. Thanks for the feedback!

[AliGW]

Here's another option:

=INDEX(B1:F1,MATCH(TRUE,INDEX((B1:F1<>0),0),0))