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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies