CODEWITHSUNDEEP
excelexcel-formulalookup
trixxd
trixxd

Reputation: 11

Lookup a specific value in a ROW & find the value in the horizontally adjacent cell

I need to lookup a value in Sheet1!B1 & find it within a single row range on Sheet2 (Sheet2!E2:DD2). Once found, I need to grab the value of the next cell in that same single row range (Sheet2!E2:DD2) & return it in Sheet1!B2.

I then want to be able to do the same again for the next row. Lookup the value in Sheet1!B1 & find it within the next single row range on Sheet2 (Sheet2!E3:DD3). Once found, I need to grab the value of the next cell in that same single row range (Sheet2!E3:DD3) & return it in cell Sheet1!B3.

& then I would repeat this formula down the Sheet1!B column for another 3000 or so rows..

What is important to note is that the value in Sheet1!B1 does not appear in the same column on each row in Sheet2 (eg. in row 2 it might appear in Sheet2!P2 & row 3 it might appear in Sheet2!BT3)

Example of scenario & desired result:

Sheet1!B1 = "Hello"
Sheet2!P2 = "Hello"
Sheet2!Q2 = "Bonjour"
Sheet2!BT3 = "Hello"
Sheet2!BU3 = "Aloha"

Desired Result in Sheet1!B2 = "Bonjour"
Desired Result in Sheet1!B3 = "Aloha"

As you can probably see, a Vlookup in Sheet1!B2 such as =VLOOKUP(Sheet1!B1, Sheet2!E2:DD2, 2, false) will not work because the lookup value (Sheet1!B1) is not found in the first column of the lookup range (Sheet2!E2:DD2), it is instead found somewhere in the middle of the range. So a vlookup will just return #N/A

I am ideally looking for a formula solution, not VBA if possible.

Upvotes: 0

Views: 2288

Answers (1)

Andreas
Andreas

Reputation: 23958

You can use INDEX and MATCH.

Match will find the position of what you search for.
In this case we search for "Hello", in E2:DD2, because we find "Hello" in P2, the return is 12 (12 columns to the right).
But we want the next adjacent cell, so we add 1.

Now to actually get the value in 13 columns to the right, we use INDEX.
Index will create a list of all items from E2 to DD2 which would be 11 empty, then "Hello", and then "Bonjour".
Index arguments is the list or matrix, row, column.
The list is E2:DD2, row is the number of rows you want to move (up or down), columns is how many columns you want to move (we want 13).

So combining the two functions will be: 

=INDEX(E3:DD3,0,MATCH($B$1,E3:DD3,0)+1)

Which mean we index the list, and we return on the same row, but the column where we find "Hello" +1.

enter image description here

Edit: I didn't notice it was on two different sheets. But the same formula applies, just the sheet needs to be added in the formula.

=INDEX(Sheet2!E2:DD2,0,MATCH($B$1,Sheet2!E2:DD2,0)+1)

Upvotes: 0

Related Questions