Formula is correct, but cell is frustratingly blank!

[DaraMurray]

Hi everyone,

This is a new one for me, and boy is it frustrating! I am using an Index Match combo using unique ID#s to fill in information from Sheet 1 to Sheet 2... that much I can do! What I can't seem to figure out is why one ID number in the middle of my data set refuses to work. Here's what I've tried:

• Checking that the cells are formatted the same way (they are general, but I also tried text just to see if that would work... it didn't).
• Rewriting the ID number after clearing it out
• Writing the ID number at the end of the dataset, just to get a fresh start
• Cutting and pasting the information to a different spot
• Removing the ID number (this produces an ERROR in the cell I'm filling... which tells me that the formula is working)
• Making sure the text isn't white somehow (tried automatic and red... did not work)
• Checking that there is information in the cell I'm pulling from (yes, this is a complete entry, no blanks in the source sheet)
• The formula is locked with $
• The ranges include all values, I didn't accidentally leave out a row (and this ID# is in the middle)
• When I put another ID# in the same row, it works!

I'm at my wit's end, especially when after I removed the ID and there was still an error. Googling has been no help because all that comes back are results for when only the formula displays instead of the result or how to highlight blank cells.

Thanks in advance for your help!

 

[Rick Rothstein]

Hi everyone,

This is a new one for me, and boy is it frustrating! I am using an Index Match combo using unique ID#s to fill in information from Sheet 1 to Sheet 2... that much I can do! What I can't seem to figure out is why one ID number in the middle of my data set refuses to work. Here's what I've tried:

• Checking that the cells are formatted the same way (they are general, but I also tried text just to see if that would work... it didn't).
• Rewriting the ID number after clearing it out
• Writing the ID number at the end of the dataset, just to get a fresh start
• Cutting and pasting the information to a different spot
• Removing the ID number (this produces an ERROR in the cell I'm filling... which tells me that the formula is working)
• Making sure the text isn't white somehow (tried automatic and red... did not work)
• Checking that there is information in the cell I'm pulling from (yes, this is a complete entry, no blanks in the source sheet)
• The formula is locked with $
• The ranges include all values, I didn't accidentally leave out a row (and this ID# is in the middle)

I'm at my wit's end, especially when after I removed the ID and there was still an error. Googling has been no help because all that comes

Show us the formula AND show us the ID number giving you trouble as well.

 

[DaraMurray]

Here is the formula, but I don't feel comfortable sharing the ID because it's not random or anonymous, unfortunately. Here's basically what it looks like (though some of my ID numbers are just numbers or a mix of numbers and letters):

My set up is basically like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]City[/TD]
[TD]ID #[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Newark[/TD]
[TD]dmurray1[/TD]
[/TR]
</tbody>[/TABLE]

I need to fill in the country and city using the ID. I'm using index match because both sheets have a ton of columns and eventually I'll need to fill them all in (to the left and right of the ID) and I like to leave the source data as intact as possible so I left the unique ID somewhere in the middle which is how it came to me.

=INDEX('[Sheet 1.xlsx]Raw Data'!$AN$2:$AN$484,MATCH(H242,'[Sheet 1.xlsx]Raw Data'!$D$22:$D$484,0))

 

[DaraMurray]

I also don't think this has anything to do with duplicates because the nature of the data is such that each ID# has two entries so every ID is duplicated.

 

[Joe4]

Your ranges don't appear to the the same size. They seem to start on different rows. A typo, perhaps?

=INDEX('[Sheet 1.xlsx]Raw Data'!$AN$[COLOR=#ff0000][B]2[/B][/COLOR]:$AN$484,MATCH(H242,'[Sheet 1.xlsx]Raw Data'!$D$[COLOR=#ff0000][B]22[/B][/COLOR]:$D$484,0))

 

[DaraMurray]

Thank you! I feel like a gigantic idiot. It's always a super small typo that your brain glosses over, isn't it? uhhhhhg. Thanks!!

 

[Joe4]

You are welcome!

It's always a super small typo that your brain glosses over

Its often the case. It happens to all of us. I find that if I look at things enough times, my brain sees what it expects to see, and not what it actually there.
Sometimes, I just need to either step away from it for a while, or get a second set of eyes on it.