0

I am working with 4 columns across two sheets.

Sheet_1 contains a column of text and a corresponding column of values.

Sheet_2 contains a column of text, some of which can be matched to the text column in sheet_1 and some cannot; all rows of cells in sheet_1 text column should match a cell row of text in the sheet_2 text column, but not vice versa because sheet_2 text column contains additional text rows.

The fourth column is a blank column in sheet_2 where I want to return the value of the second column in sheet_1.

The text column in sheet_1 is not in the same order as the text column in sheet_2. All columns contain thousands of rows.

enter image description here

In the past, just a couple of days ago on the same computer and in the same Excel program, I successfully used this formula:

=IFERROR(INDEX('Sheet_1'!B:B,MATCH('Sheet_2'!A1,'Sheet_1'!A:A,0)),"Not Found")

In the past use of this formula, it would successfully match the text of number in sheet_2, cell A1 to the text or number in sheet_1, column A, return the corresponding value or number in the cell from the indexed column B in sheet_2 to the cell I placed the formula. If there was not a match found, then the formula would return the text "Not Found."

Even if I go back to where I have used the formula successfully and retype the exact same formula OR cut and paste it, sometimes it will work and sometimes it will not.

I have tried reformatting all the columns to text using the text to columns function. I have tried copying into Notepad and then pasting back into the worksheet. I have tried different variations of formulas I have found online and cannot get anything to work.

At this point if I get a "N/A" instead of "Not Found," I would be happy.

Please help.

Thank you, Cooper

|improve this question
1

Without access to the original spreadsheet, I'm not sure how much we can help with an intermittent issue. I would advise that you break the formula into its components (you can hide the columns if required) so you can see where the error is being introduced.

So for example on Sheet2 in column C put =MATCH(...), then in column D put =INDEX(...) and so on: enter image description here

|improve this answer
  • Okay, I am trying what you suggested, very good suggestion might I add, I did not think about breaking the pieced apart into separate formulas. – Cooper Jul 5 '17 at 1:07
  • Anyway, it looks like my worksheet is not recognizing the "=" as a signal to start a formula. Up in the fx line, the formulas still highlights, etc., however, when I leave the cell I write the formula in, I only see the text of the formula like it is just text and not a hidden formula. I do not get any kind of any error message or other response to my formula. – Cooper Jul 5 '17 at 1:09
  • Ensure your cell format is General and not Text. After changing to General, edit the cell and accept, it should re-evaluate the formula. – Ian Jul 5 '17 at 1:12
  • This is happening with even the simplest formula of =A1+A2 where A1 has the number 2 in it and A2 has the number 2 in it. I do not get 4. What I see when I leave the cell where I wrote the formula is =A1+A2. – Cooper Jul 5 '17 at 1:13
  • If I create a fresh new sheet in this same workbook, I do not have the same issue. I am working with so much data that it is not very easy to move. Plus, I have already pasted into notebook and back again. Any suggestions? – Cooper Jul 5 '17 at 1:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.