Returning Fields after "x" on each row.

[QuickJeff]

I've tried a few different things. I would like to search horizontally by row for "x", then return the values from a couple of cells nest to the X.

Messed with HLOOKUP and V LOOKUP, yet I haven't figured out if it's appropriate for this.
All I need is someone to point me in the right direction : )

 

[AliGW]

Can there be more than one x on each row?

 

[AliGW]

This might get you started:

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I	J
2	1		Triangles	a		Squares	a	x	Circles	a
3	2	x	Triangles	b		Squares	b		Circles	b
4	3		Triangles	c	x	Squares	c		Circles	c
5	4	x	Triangles	d		Squares	d		Circles	d
6	5		Triangles	e	x	Squares	e		Circles	e
7	6		Triangles	f		Squares	f	x	Circles	f
8	7	x	Triangles	g		Squares	g		Circles	g
9
10
11	1	Circles	a
12	2	Triangles	b
13	3	Squares	c
14	4	Triangles	d
15	5	Squares	e
16	6	Circles	f
17	7	Triangles	g
Sheet1

Cell Formulas
Range	Formula
B11	=INDEX($B2:$J2,,MATCH("x",$B2:$J2,0)+1)
C11	=INDEX($B2:$J2,,MATCH("x",$B2:$J2,0)+2)

 

[QuickJeff]

Get me started? You way surpassed that. Thank you!

I never really have understood the index function, yet I use it heavily from a formula I was once given. I think I'll take the time to learn a bit more. Happy new year 2017 : )

 

[AliGW]

ADVERTISEMENT

You're welcome!

 

[QuickJeff]

I'm getting better at it. I came to the realization that "A" may not also be there, in which case I get the dreaded eyesore #N/A.

How would I go about adding a if statement like this
=IF('Measure Sheet'!C24=" "," ",

to an index like this
=INDEX($CB25:$XFD25,,MATCH("A",$CB25:$XFD25,0)+8)

If've tried a few combinations, but I can't seem to figure it out!

 

[AliGW]

ADVERTISEMENT

Like this:

=IF('Measure Sheet'!C24="","",INDEX($CB25:$XFD25,,MATCH("A",$CB25:$XFD25,0)+8))

Use "", not " ".

 

[QuickJeff]

Like this:

=IF('Measure Sheet'!C24="","",INDEX($CB25:$XFD25,,MATCH("A",$CB25:$XFD25,0)+8))

Use "", not " ".

Haha, that was an easy fix. I realized I had a few formulas were incorrect without the space between the two. Thank you for all the help!

 

[AliGW]

My pleasure.

 

[QuickJeff]

You know. I ran into what I thought I would!

If there is no match in the index, how would I return a blank instead of the #N/A.
(I tried using the index itself, but didn't get very far. I imagine the fix is an IFERROR condition, but I could be wrong.)

=INDEX($BY25:$XFD25,,MATCH("B",$BY25:$XFD25,0)+2))