Use Index and Match Functions in Formula to get matching Data

Hi i have a complicated formula question that i cant figure out. Im trying to make a formula as such:
If a specific cell is blank leave this cell blank, if it CONTAINS a specific code from a table of codes (column one of a table), then insert the corresponding value (column two of the same table), if not then insert the number 6

By: Bash

2 thoughts on “Use Index and Match Functions in Formula to get matching Data

  1. Excel Geek

    Try this formula…

    =(IF(ISBLANK(A2),"",IF(ISERROR(INDEX($C$2:$D$5,MATCH(A2,$C$2:$C$5,FALSE),2)),6,INDEX($C$2:$D$5,MATCH(A2,$C$2:$C$5,FALSE),2))))

    I have assumed the columns that contains your code from the table is column A, and it starts from A2. I have assumed your table of codes and corresponding value is in cells C2 to D5.

    You will need to change the references accordingly to match your spreadsheet.

    🙂

  2. John Donadio

    Good afternoon

    I happened on to your link after many hours of searching for an answer to my problem. I’m using an Indexing formula and need to wrap a Hyperlink function in my formula. My workbook is keeping an activity log with worksheets linked with formulas that pull data to a master report worksheet. I used the hyperlink function to link a PDF file to a cell in a worksheet which stores data for the master worksheet the link works great however, the problem is when I pull data to the master worksheet to generate a report the hyperlink file appears but does not open the PDF file.

    Any help would be greatly appreciated. I’ve spent the many hours trying to make this work.

    Thank you in advance

    John

Leave a Reply

Your email address will not be published. Required fields are marked *