Home

Need help index/match formula

%3CLINGO-SUB%20id%3D%22lingo-sub-770494%22%20slang%3D%22en-US%22%3ENeed%20help%20index%2Fmatch%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-770494%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20the%20attached%20file%2C%20I%20am%20looking%20for%20a%20formula%20in%20cell%20F18%20that%20pulls%20in%20the%20latest%20(based%20on%20date)%20value%20for%20B18.%26nbsp%3B%20%26nbsp%3BFor%20the%20latest%20date%2C%20if%20B18%20is%20matched%20in%20Column%20B%2C%20then%20pull%20the%20value%20from%20Column%20H.%26nbsp%3B%20If%20the%20B18%20is%20matched%20with%20Column%20C%2C%20then%20pull%20the%20value%20from%20Column%20I.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EIn%20this%20specific%20example%2C%20the%20formula%20in%20F18%20should%20pull%20the%20value%20form%20I10.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F28910%22%20target%3D%22_blank%22%3E%40Ashish%20Mathur%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-770494%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-771200%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20index%2Fmatch%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-771200%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380236%22%20target%3D%22_blank%22%3E%40inmanc%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20formula%20in%20F18%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(COUNTIF(C%242%3AC18%2CB18)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ELOOKUP(2%2C1%2F(C%242%3AC18%3DB18)%2CI%242%3AI18)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ELOOKUP(9.9E%2B307%2CH%242%3AH18))%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-771702%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20index%2Fmatch%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-771702%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380236%22%20target%3D%22_blank%22%3E%40inmanc%3C%2FA%3E%26nbsp%3B-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFind%20the%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20769px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124312iC81631AC054EDD41%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22example-index-match.PNG%22%20title%3D%22example-index-match.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-771950%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20index%2Fmatch%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-771950%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20works.%26nbsp%3B%20However%2C%20there%20might%20be%20times%20when%20I%20need%20to%20the%20latest%20value%20for%20B18%20may%20be%20in%20column%20B%20or%20Column%20C.%26nbsp%3B%20How%20would%20I%20adjust%20this%20formula%20to%20look%20up%20either%20column%3F%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F303863%22%20target%3D%22_blank%22%3E%40ChrisMendoza%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772211%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20index%2Fmatch%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772211%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380236%22%20target%3D%22_blank%22%3E%40inmanc%3C%2FA%3E%26nbsp%3B-%3C%2FP%3E%3CP%3EYou'll%20have%20to%20work%20out%20the%20logic%2C%20something%20along%20the%20lines%20of%20the%20attached.%20This%20should%20be%20a%20good%20start.%20I%20used%26nbsp%3B%3CEM%3Estructured%20references%3C%2FEM%3E%26nbsp%3Bas%20it%20was%20getting%20really%20ugly%20really%20fast.%20Learn%20more%20about%20that%20at%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-gb%2Farticle%2Fusing-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-gb%2Farticle%2Fusing-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124361i9FBDAFA18D7AB59A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22example-index-match-2.PNG%22%20title%3D%22example-index-match-2.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
inmanc
New Contributor

In the attached file, I am looking for a formula in cell F18 that pulls in the latest (based on date) value for B18.   For the latest date, if B18 is matched in Column B, then pull the value from Column H.  If the B18 is matched with Column C, then pull the value from Column I. 


In this specific example, the formula in F18 should pull the value form I10.  

 

@Ashish Mathur 

4 Replies

@inmanc 

Try this formula in F18: 

=IF(COUNTIF(C$2:C18,B18),
LOOKUP(2,1/(C$2:C18=B18),I$2:I18),
LOOKUP(9.9E+307,H$2:H18))

@inmanc -

 

Find the attached file.

 

example-index-match.PNG

This works.  However, there might be times when I need to the latest value for B18 may be in column B or Column C.  How would I adjust this formula to look up either column?@ChrisMendoza 

@inmanc -

You'll have to work out the logic, something along the lines of the attached. This should be a good start. I used structured references as it was getting really ugly really fast. Learn more about that at https://support.office.com/en-gb/article/using-structured-references-with-excel-tables-f5ed2452-2337...

 

example-index-match-2.PNG

 

 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies