Need help please on query

%3CLINGO-SUB%20id%3D%22lingo-sub-1385069%22%20slang%3D%22en-US%22%3ENeed%20help%20please%20on%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1385069%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20All%2C%3CBR%20%2F%3EIn%20English%2C%20I%20want%20to%20populate%20column%20J4%20based%20on%20the%20table%20value%20(see%20attached%20image)%3C%2FP%3E%3CP%3EIf%20IA%20%3D%20WDDS%20the%20populate%20with%20table%20WDDS%20value%206%3A00%3CBR%20%2F%3EIf%20IA%20%3D%20WDAS%20the%20populate%20with%20table%20WDAS%20value%2012%3A00%3CBR%20%2F%3EIf%20IA%20%3D%20WDNS%20the%20populate%20with%20table%20WDNS%20value%2020%3A00%3CBR%20%2F%3EIf%20IA%20%3D%20WEDS%20the%20populate%20with%20table%20WEDS%20value%208%3A00%3CBR%20%2F%3EIf%20IA%20%3D%20WENS%20the%20populate%20with%20table%20WENS%20value%2020%3A00%3CBR%20%2F%3EIf%20IA%20%3D%20SAL%20the%20populate%20with%20table%20SAL%20value%208%3A00%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1385069%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1385367%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20please%20on%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1385367%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F665783%22%20target%3D%22_blank%22%3E%40LanceP%3C%2FA%3E%26nbsp%3BIn%20J4%2C%20that%20could%20be%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(E4%3AE9%2CMATCH(I4%2CF4%3AF9%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eor%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DXLOOKUP(I4%2CF4%3AF9%2CE4%3AE9%2C%22%22%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eif%20your%20Excel%20version%20supports%20XLOOKUP%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1385604%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20please%20on%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1385604%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F665783%22%20target%3D%22_blank%22%3E%40LanceP%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20can%20be%20done%20by%20using%20INDEX%20MATCH%20function.%3C%2FP%3E%3CP%3E%3DINDEX(E4%3AE9%2CMATCH(I4%2CF4%3AF9%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMake%20sure%20you%20extend%20the%20data%20range%20in%20case%20more%20records%20are%20added.%20Hope%20this%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1385735%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20please%20on%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1385735%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F665807%22%20target%3D%22_blank%22%3E%40ShishirKumar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat's%20the%20difference%20with%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bsuggestion%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1385758%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20please%20on%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1385758%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENothing.%20I%20just%20saw%20the%20XLOOKUP%20thing.%20Missed%20the%20above%20point.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

i All,
In English, I want to populate the cell J4 based on the table value (see attached image)

If cell IA = WDDS the populate with table WDDS value 6:00
If cell IA = WDAS the populate with table WDAS value 12:00
If cell IA = WDNS the populate with table WDNS value 20:00
If cell IA = WEDS the populate with table WEDS value 8:00
If cell IA = WENS the populate with table WENS value 20:00
If cell IA = SAL the populate with table SAL value 8:00

 

Thanks in advance

4 Replies

@LanceP In J4, that could be:

 

=INDEX(E4:E9,MATCH(I4,F4:F9,0))

 

or

=XLOOKUP(I4,F4:F9,E4:E9,"",0)

if your Excel version supports XLOOKUP 

@LanceP 

This can be done by using INDEX MATCH function.

=INDEX(E4:E9,MATCH(I4,F4:F9,0))

 

Make sure you extend the data range in case more records are added. Hope this helps.

@ShishirKumar 

What's the difference with @Riny_van_Eekelen suggestion?

@Sergei Baklan 

Nothing. I just saw the XLOOKUP thing. Missed the above point.