# Horizontal lookup based on vertical lookup

#### Mxxa

##### New Member
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?

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Example:

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

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?

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.

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
ABCDEFGHIJ
1NameResponseResponseResponseResponseResponseValueXValueYResult
2BarryAppleOrangeMelonGrapePawpawAgnieszkaMelon
3PaulAppleOrangeMelonGrapePawpawBarryApple
4AmyAppleOrangeMelonGrapePawpawHelenGrape
5LouiseBananaPineappleStrawberryKiwiPawpawPaulPineapple
6HelenAppleOrangeMelonGrapePawpawRachelGrape
7RachelApplePineappleMelonKiwiPawpawRossApple
8AgnieszkaAppleOrangeMelonGrapePawpaw
9RossApplePineappleMelonGrapePawpaw
10PeterApplePineappleMelonGrapePawpaw
11FauAppleOrangeMelonGrapePawpaw
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.

Oh, I see. In J2 copied down:

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

Thank you. That did the trick.

Thanks again.

Replies
4
Views
220
Replies
18
Views
997
Replies
1
Views
150
Replies
17
Views
571
Replies
0
Views
463

1,203,096
Messages
6,053,516
Members
444,669
Latest member
Renarian

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back