How to check value on Sheet2 and get in Sheet1

[malabond]

Hello friends,

I'm looking for formula to get values from a range in Sheet2 based on text of a cell.

For ex.

in Sheet1, I want to check if the text of A2 exist on text that is from B1:GD1 in Sheet2, and if the text is in T1, then get value of T17 in my cell in Sheet1.

In attachment you will find my file.

Thanks for you support.

FORMULA.xlsx

[boopathiraja]

Hi, Try this

Please Login or Register  to view this content.

[malabond]

Hi, Try this

Please Login or Register  to view this content.

I tried this but it is not working.

[Richard Buttrey]

Hi,

I presume the reference to column T was just an example and that you want the row 17 value for text other than 04.03.9

In which case in say D2 on sheet1 copied down

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

The reasons the HLOOKUPS are not working is not because it's a HLOOKUP per se, rather that you have a leading character space in your row 1 values on sheet 2 which of course needs to be included in any Lookup or Match function.

Alternatively of course remove the leading space characters.

[Pete_UK]

You have a lot of leading spaces in row 1 of Sheet2, so it is difficult to find exact matches. You can try this:

=IFERROR(INDEX(Sheet2!$17:$17,MATCH("*"&A2,Sheet2!$1:$1,0)),"")

where the asterisk is the wildcard character meaning to include anything at the beginning, but this may give you unexpected results.

Hope this helps.

Pete

[sakmsb]

Hello friends,

I'm looking for formula to get values from a range in Sheet2 based on text of a cell.

For ex.

in Sheet1, I want to check if the text of A2 exist on text that is from B1:GD1 in Sheet2, and if the text is in T1, then get value of T17 in my cell in Sheet1.

In attachment you will find my file.

Thanks for you support.

Attachment 352980

Edited:- Meant to post on a diff thread, apologies.