Lookup across multiple columns

[testconsultancy]

Hi there,

I have a big list of data, across multiple columns. I need a formula
that search a specific value that is sitting in column X. If found, it
should return the value that is on the same row in column H.
The data can be found on the left side of column H (A-G) or on the
right side of column H (I-W).

Appreciate your help.

 

[Dave Peterson]

I'd start at Debra Dalgleish's site:
http://contextures.com/xlFunctions03.html

 

[testconsultancy]

Hi Dave,

Thanks for pointing me to that website, Very usefull. However, I can't
find a solution to my case. The formula should look in several
columns. If found, it should take the value that is on the same row
and in column H. Since the found value could be on the left or the
right of column H, it should somehow look to the left or the right. I
don't know how to do that even with the examples on that website...

 

[Dave Peterson]

=index('sheet2'!a:a,match(a1,'sheet2'!h:h,0))
would return the value in column A of sheet2 when the value in A1 matches the
value in column H of sheet2.

Change A:A to whatever column you need to return.
Leave H:H as the column to match.
Change A1 to the cell that contains the value to match.
Change 'sheet2' to the worksheet that contains the table.

If this doesn't help, post the formula you tried and more details for a specific
example.