Match & Index function - Cell format problem that is keeping it fromworking for me

[Mike C]

Hello - I am trying to use the Match and index functions together.
Typically, I have been able to match and pull in the data without a
problem.

But now that I am using a text file that I export into excel, I am
running into problems. Basically, the formula won't work (even though
I have tried trimming the data, changing all columns to text, general,
and number.

The only way that the formula works is when I copy and paste the
actual reference cell into the reference array from the range that I
am seeking to pull in the data.

So for example, my formula reads: =INDEX(Sheet2!B:B,MATCH(Sheet1!
A419,Sheet2!A:A,FALSE)),

but the only way that I get the data from Sheet2 column B is by
physically pasting Sheet1!A419 into column A in in Sheet 2. Then and
only then will the values from Column B in sheet2 appear in Sheet 1--
as the formula is intended to do.

Is anyone familiar with this problem? Does the function typically
work better when the sheets are formatted as general, versus number,
versus text?

Thanks for any suggestions.

(Also, another problem I am experiencing with the files----and it may
be related-----is that the formulas will not work altogether. That is,
any formula that I punch into the cell does not produce a value, but
instead the cell shows the formula when I press enter. However, this I
am usually able to fix by formatting the book as general and then
retyping the formula in the cells).

 

[Niek Otten]

Still sounds like a text vs number problem.
If cells look like numbers but actually are text, it is not enough to format them as Number or General; they have to be
re-entered.
Make sure both the search argument and the MATCH table are treated this way.
Options:
1. Format the cell as Number. Hit F2, ENTER.
2. Format an empty cell as number. Edit>Copy. Select your "numbers". Paste special, check Add.

You can always check with the ISTEXT() or ISNUMBER() functions.

If you have no success, check this:

=========================================================
Vlookup gives wrong answer

Niek Otten, April 1 2006

Frequent causes:

1. Some cells look like numbers, but are actually text. You can check with the ISTEXT function.
Check both the search arguments and the lookup table.
Formatting as numbers afterwards doesn't help.
Remedy:
Format an empty cell as Number. Enter the number 1. Edit>Copy. Select your "numbers". Edit>Paste Special, check Multiply.

2. The data is not sorted ascending and the 4th argument of the VLOOKUP is TRUE or is omitted.

3. There are spaces or other invisible characters in either the search arguments or the lookup table.
This often happens when you import data from other applications.
Use the LEN() function to see how many characters there really are in the cell and compare that with what you see.
Use the TRIM function to remove all spaces except single spaces between words.
Use the CLEAN function to remove all nonprintable characters. HTML characters can be removed with a macro by David
McRitchie,
which can be downloaded here: http://www.mvps.org/dmcritchie/excel/join.htm#trimall

4. The formula was copied from somewhere else, but the addresses of the lookup table were not absolute so have changed in
the Paste process
and now point to the wrong range.
Use absolute addresses for the lookup table, like $A$1:$B$20 instead of relative addresses like A1:B20.
When editing or entering a formula, use the F4 key to toggle between several forms of relative addresses and absolute ones
(normally only for the table, not for the search argument, but this depends on your specific problem).
The first hit of F4 changes the default relative address to an absolute one. That is usually what you need.
Even better: use a Defined Name for the table instead of cell addresses; Insert>Name>Define.

5. The table was extended after its initial use, but the definition of the table in the VLOOKUP or in the Defined Name was
not adjusted accordingly.
To prevent this from happening: always use explicit bottom and top rows (with dummy error values if necessary)
and insert new rows or cells between those two.
Then the definition of the range or the Defined Name will adjust automatically.
Users of Excel 2003 and higher may profit from the List feature in the Data menu to maintain tables.
=========================================================


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Hello - I am trying to use the Match and index functions together.
| Typically, I have been able to match and pull in the data without a
| problem.
|
| But now that I am using a text file that I export into excel, I am
| running into problems. Basically, the formula won't work (even though
| I have tried trimming the data, changing all columns to text, general,
| and number.
|
| The only way that the formula works is when I copy and paste the
| actual reference cell into the reference array from the range that I
| am seeking to pull in the data.
|
| So for example, my formula reads: =INDEX(Sheet2!B:B,MATCH(Sheet1!
| A419,Sheet2!A:A,FALSE)),
|
| but the only way that I get the data from Sheet2 column B is by
| physically pasting Sheet1!A419 into column A in in Sheet 2. Then and
| only then will the values from Column B in sheet2 appear in Sheet 1--
| as the formula is intended to do.
|
| Is anyone familiar with this problem? Does the function typically
| work better when the sheets are formatted as general, versus number,
| versus text?
|
| Thanks for any suggestions.
|
| (Also, another problem I am experiencing with the files----and it may
| be related-----is that the formulas will not work altogether. That is,
| any formula that I punch into the cell does not produce a value, but
| instead the cell shows the formula when I press enter. However, this I
| am usually able to fix by formatting the book as general and then
| retyping the formula in the cells).

 

[Pete_UK]

The formats have to be the same on both sheets for an exact match to
work, i.e. both should be numbers or both should be text. You might
like to try these variations:

=INDEX(Sheet2!B:B,MATCH(Sheet1!A419&"",Sheet2!A:A,FALSE))

if Sheet1 values are numbers but Sheet2 are text, or:

=INDEX(Sheet2!B:B,MATCH(Sheet1!A419*1,Sheet2!A:A,FALSE))

if Sheet1 values are text and Sheet2 values are numbers.

Another problem with text values is that you could have leading/
trailing or multiple spaces, but if you have applied the TRIM function
you should have got rid of those. However, you may have the non-
breaking space character (code 160), and this is not removed with
TRIM.

Hope this helps.

Pete

 

[Tyro]

Formatting is the visual display of the cell contents for human consumption.
Excel looks at the actual cell value when you use the cell in a computation,
lookup, match etc. You might want to look at the results of your formula
with formula evaluation. Step through the formula and see the results.
You're confusing formatting with cell values. For example, if the cell
contains the number 39515 and I format the cell as a number, I see 39515. If
I format the cell as a date, I see March 8, 2008.

Tyro

 

[Mike C]

Formatting is the visual display of thecellcontents for human consumption.
Excel looks at the actualcellvalue when you use thecellin a computation,
lookup,matchetc. You might want to look at the results of your formula
with formula evaluation. Step through the formula and see the results.
You're confusing formatting withcellvalues.  For example, if thecell
contains the number 39515 and Iformatthecellas a number, I see 39515. If
Iformatthecellas a date, I see March 8, 2008.

Tyro













- Show quoted text -

thanks to all for your replies! I will hopefully get it right......

 

[Tyro]

To see the cell contents, press Ctrl+Accent grave (`), the key above the tab
key. Press Ctrl+Accent grave to return to normal display

Tyro

Formatting is the visual display of thecellcontents for human consumption.
Excel looks at the actualcellvalue when you use thecellin a computation,
lookup,matchetc. You might want to look at the results of your formula
with formula evaluation. Step through the formula and see the results.
You're confusing formatting withcellvalues. For example, if thecell
contains the number 39515 and Iformatthecellas a number, I see 39515. If
Iformatthecellas a date, I see March 8, 2008.

Tyro













- Show quoted text -

thanks to all for your replies! I will hopefully get it right......