none
How can I convert this into an INDEX MATCH? RRS feed

  • Question

  • Hello all

    I have been using a file with a COUNTIF feature. I would like to change it to an INDEX MATCH but I'm vastly unfamiliar with that function. 

    The purpose is so that we can sort the file and not have the one column with the formula get messed up everytime we sort. 

    =IF(COUNTIF('IAE Awarded'!$A$2:$A$50000,RepairsReleasedbyDSP!F2),"Bid Awarded", "N/A")

    If someone could guide me onto how to work this into an INDEX MATCH and explain the ins and outs so I understand I'd be in debt to you fine sir or madam.

    In essence the formula looks to Column F and if Column Fs value is present on the "IAE Awarded" worksheet then Bid Awarded if not then N/A
    • Edited by stillanoob Wednesday, September 26, 2018 6:15 PM
    Wednesday, September 26, 2018 6:15 PM

All replies

  • You could use MATCH in combination with ISNUMBER; no need to use INDEX.

    The formula

    =MATCH(RepairsReleasedbyDSP!F2,'IAE Awarded'!$A$2:$A$50000,0)

    will return the index number of the first matching cell if there is one, and #N/A if not. So

    =ISNUMBER(MATCH(RepairsReleasedbyDSP!F2,'IAE Awarded'!$A$2:$A$50000,0))

    will return TRUE if there is a match, FALSE if not. The complete formula becomes

    =IF(ISNUMBER(MATCH(RepairsReleasedbyDSP!F2,'IAE Awarded'!$A$2:$A$50000,0)),"Bid Awarded","N/A")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, September 26, 2018 7:00 PM
  • Hans, thank you for your explanation. I tried your formula and understand it a bit but it doesn't allow me to sort columns in the worksheet. The formula points to the same exact row and doesn't somehow transfer over. 

    I read on a forum somewhere that using the INDEX MATCH function that would NOT mess up when sorting columns as it apparently is an issue for a lot of other people. 

    Can you confirm or unconfirm that INDEX MATCH would allow sorting of columns without breaking the formula? If so could you be so kind to provide the correct syntax with my given example as you did above?

    Thanks,

    Josh

    Thursday, September 27, 2018 10:38 AM
  • Is RepairsReleasedbyDSP the sheet containing the formulas?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, September 27, 2018 10:56 AM
  • Yes, that's the worksheet with the formula. And it's literally only one column in that worksheet that contains a formula which is why I'm trying to find a solution to the sorting issue.
    Thursday, September 27, 2018 2:48 PM
  • Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

    Or register at www.eileenslounge.com (it's free) and start a thread in the Excel forum. You can attach files up to 250 KB to a post there (zipped if necessary).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, September 27, 2018 7:46 PM
  • Yes, I can. Do you have an email address I could send it to perhaps???
    Monday, October 1, 2018 11:44 AM
  • You can send it to hans dot vogelaar at gmail dot com

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, October 1, 2018 1:54 PM