How to match text and return value in adjacent or next cell?

%3CLINGO-SUB%20id%3D%22lingo-sub-1922203%22%20slang%3D%22en-US%22%3EHow%20to%20match%20text%20and%20return%20value%20in%20adjacent%20or%20next%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1922203%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20on%20reporting.%20How%20can%20I%26nbsp%3Bmatch%20text%20and%20return%20value%20in%20adjacent%20or%20next%20cell%3F%3C%2FP%3E%3CP%3EFor%20example%2C%20I%20want%20to%20match%20%22WTM%22%20on%20each%20day%20and%20return%20value%20next%20to%20it.%3C%2FP%3E%3CP%3EOn%201%20Jan%2C%20I%20should%20get%2033.1.%3C%2FP%3E%3CP%3EOn%202%20Jan%2C%20I%20should%20get%2034.5.%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22960%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2219%22%3E01-Jan%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EWTM%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E33.1%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EAZB%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E25.6%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ECAB%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E19.9%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EDAL%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E8.2%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EJSM%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E6.6%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ESIB%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E3.3%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EKIM%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E1.1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E02-Jan%3C%2FTD%3E%3CTD%3ECAB%3C%2FTD%3E%3CTD%3E35.9%3C%2FTD%3E%3CTD%3EWTM%3C%2FTD%3E%3CTD%3E34.5%3C%2FTD%3E%3CTD%3EAZB%3C%2FTD%3E%3CTD%3E20.5%3C%2FTD%3E%3CTD%3EDAL%3C%2FTD%3E%3CTD%3E4.3%3C%2FTD%3E%3CTD%3EKIM%3C%2FTD%3E%3CTD%3E1.2%3C%2FTD%3E%3CTD%3ESIB%3C%2FTD%3E%3CTD%3E1.0%3C%2FTD%3E%3CTD%3EDUS%3C%2FTD%3E%3CTD%3E0.7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E03-Jan%3C%2FTD%3E%3CTD%3ECAB%3C%2FTD%3E%3CTD%3E35.9%3C%2FTD%3E%3CTD%3EWTM%3C%2FTD%3E%3CTD%3E34.5%3C%2FTD%3E%3CTD%3EAZB%3C%2FTD%3E%3CTD%3E20.5%3C%2FTD%3E%3CTD%3EDAL%3C%2FTD%3E%3CTD%3E4.3%3C%2FTD%3E%3CTD%3EKIM%3C%2FTD%3E%3CTD%3E1.2%3C%2FTD%3E%3CTD%3ESIB%3C%2FTD%3E%3CTD%3E1.0%3C%2FTD%3E%3CTD%3EDUS%3C%2FTD%3E%3CTD%3E0.7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E04-Jan%3C%2FTD%3E%3CTD%3ECAB%3C%2FTD%3E%3CTD%3E31.1%3C%2FTD%3E%3CTD%3EWTM%3C%2FTD%3E%3CTD%3E25.9%3C%2FTD%3E%3CTD%3EAZB%3C%2FTD%3E%3CTD%3E20.6%3C%2FTD%3E%3CTD%3EDAL%3C%2FTD%3E%3CTD%3E5.6%3C%2FTD%3E%3CTD%3EBUO%3C%2FTD%3E%3CTD%3E4.4%3C%2FTD%3E%3CTD%3EJSM%3C%2FTD%3E%3CTD%3E3.9%3C%2FTD%3E%3CTD%3ESIB%3C%2FTD%3E%3CTD%3E3.6%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E05-Jan%3C%2FTD%3E%3CTD%3ECAB%3C%2FTD%3E%3CTD%3E26.2%3C%2FTD%3E%3CTD%3EAZB%3C%2FTD%3E%3CTD%3E20.6%3C%2FTD%3E%3CTD%3EWTM%3C%2FTD%3E%3CTD%3E17.3%3C%2FTD%3E%3CTD%3EBUO%3C%2FTD%3E%3CTD%3E8.8%3C%2FTD%3E%3CTD%3EJSM%3C%2FTD%3E%3CTD%3E7.8%3C%2FTD%3E%3CTD%3EDAL%3C%2FTD%3E%3CTD%3E6.8%3C%2FTD%3E%3CTD%3ESIB%3C%2FTD%3E%3CTD%3E6.3%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20all%20for%20support.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1922203%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1922296%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20match%20text%20and%20return%20value%20in%20adjacent%20or%20next%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1922296%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F878209%22%20target%3D%22_blank%22%3E%40Pisut%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DXLOOKUP(%22WTM%22%2CB1%3AN1%2CC1%3AO1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EBut%20it%20is%20better%20the%20change%20the%20structure%20of%20your%20data%3A%3C%2FP%3E%3CP%3EDate%20-%20Category%20-%20Value%3C%2FP%3E%3CP%3EThis%20enables%20you%20to%20use%20SUMIFS()%2C%20COUNTIFS()%20or%20pivot%20table%20or%20just%20filter%20or%20sort.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am working on reporting. How can I match text and return value in adjacent or next cell?

For example, I want to match "WTM" on each day and return value next to it.

On 1 Jan, I should get 33.1.

On 2 Jan, I should get 34.5.

01-JanWTM33.1AZB25.6CAB19.9DAL8.2JSM6.6SIB3.3KIM1.1
02-JanCAB35.9WTM34.5AZB20.5DAL4.3KIM1.2SIB1.0DUS0.7
03-JanCAB35.9WTM34.5AZB20.5DAL4.3KIM1.2SIB1.0DUS0.7
04-JanCAB31.1WTM25.9AZB20.6DAL5.6BUO4.4JSM3.9SIB3.6
05-JanCAB26.2AZB20.6WTM17.3BUO8.8JSM7.8DAL6.8SIB6.3

 

Thank you all for support.

2 Replies

@Pisut 

Try this:

=XLOOKUP("WTM",B1:N1,C1:O1)

But it is better the change the structure of your data:

Date - Category - Value

This enables you to use SUMIFS(), COUNTIFS() or pivot table or just filter or sort.

 

 

@Detlef Lewin I really appreciate your help. This help me a lot.