Nov 22 2020 06:32 PM
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-Jan | WTM | 33.1 | AZB | 25.6 | CAB | 19.9 | DAL | 8.2 | JSM | 6.6 | SIB | 3.3 | KIM | 1.1 |
02-Jan | CAB | 35.9 | WTM | 34.5 | AZB | 20.5 | DAL | 4.3 | KIM | 1.2 | SIB | 1.0 | DUS | 0.7 |
03-Jan | CAB | 35.9 | WTM | 34.5 | AZB | 20.5 | DAL | 4.3 | KIM | 1.2 | SIB | 1.0 | DUS | 0.7 |
04-Jan | CAB | 31.1 | WTM | 25.9 | AZB | 20.6 | DAL | 5.6 | BUO | 4.4 | JSM | 3.9 | SIB | 3.6 |
05-Jan | CAB | 26.2 | AZB | 20.6 | WTM | 17.3 | BUO | 8.8 | JSM | 7.8 | DAL | 6.8 | SIB | 6.3 |
Thank you all for support.
Nov 22 2020 07:55 PM
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.
Nov 23 2020 07:28 PM
@Detlef Lewin I really appreciate your help. This help me a lot.