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
Highlighted
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
Highlighted

@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))

Highlighted

@inmanc -

 

Find the attached file.

 

example-index-match.PNG

Highlighted

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 

Highlighted

@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