Horizontal lookup based on vertical lookup

[Mxxa]

Hi, my first post on this great forum that has provided lots of useful information to me in the past.

I would like to do a two way lookup, except that the horizontal lookup is not within a fixed row, it needs to be within the row returned by the vertical lookup.

E.g. I have a range A1:Z10. I must search within A:A for ValueY. ValueY is found in A4. I would then like to search for ValueX only in 4:4

Can someone suggest a formula solution?

Many thanks in advance.

 

[Andrew Poulsom]

Example:

=INDEX(B2:Z10,MATCH("ValueY",A2:A10,FALSE),MATCH("ValueX",B1:Z1,FALSE))

 

[Mxxa]

Example:

=INDEX(B2:Z10,MATCH("ValueY",A2:A10,FALSE),MATCH("ValueX",B1:Z1,FALSE))

Thanks for your reply. The second MATCH function searches B1:Z1 for ValueX, whereas I want to search the same row as where ValueY is found. In my example in the first post, that would be B4:Z4.

Any further ideas?

 

[Andrew Poulsom]

Did you try my formula? The first match returns the row number and the second match returns the column number. These are used by INDEX to return the value at the intersection.

 

[Mxxa]

Hi, yes, I did try it. Its not working for me as I need the ValueX to be matched in the same row as ValueY. Let me try to explain this a little better:

Test.xls
	A	B	C	D	E	F	G	H	I	J
1	Name	Response	Response	Response	Response	Response		ValueX	ValueY	Result
2	Barry	Apple	Orange	Melon	Grape	Pawpaw		Agnieszka	Melon
3	Paul	Apple	Orange	Melon	Grape	Pawpaw		Barry	Apple
4	Amy	Apple	Orange	Melon	Grape	Pawpaw		Helen	Grape
5	Louise	Banana	Pineapple	Strawberry	Kiwi	Pawpaw		Paul	Pineapple
6	Helen	Apple	Orange	Melon	Grape	Pawpaw		Rachel	Grape
7	Rachel	Apple	Pineapple	Melon	Kiwi	Pawpaw		Ross	Apple
8	Agnieszka	Apple	Orange	Melon	Grape	Pawpaw
9	Ross	Apple	Pineapple	Melon	Grape	Pawpaw
10	Peter	Apple	Pineapple	Melon	Grape	Pawpaw
11	Fau	Apple	Orange	Melon	Grape	Pawpaw
New

For each name, I would like to know whether ValueY is one of their Responses. The Result I would like is True or False.

 

[Andrew Poulsom]

Oh, I see. In J2 copied down:

=ISNUMBER(MATCH(I2,INDEX($B$2:$F$11,MATCH(H2,$A$2:$A$11,FALSE),0),FALSE))

 

[Mxxa]

Thank you. That did the trick.

Thanks again.