anyone have any idea why a cell may not match another identical cell? I’m using the index function to look up a value based on 2 factors- kinda like a v and h-lookup combined. so I have sheet 1 with a data point that is XY and trying to use function to look up the value in my matrix for XY. Its working for XZ and XA, etc, but its not matching the XY (#N/A error) I’ve even attempted to copy paste the factors between the sheets to make it recognize them and its still stuck! Looked for spaces, differences in formatting, etc. Any idea’s??
It may be the formatting issue where one is a number and the other is text. Changing the number format doesn’t always work for some reason. Try checking to see if they are values or text using the ISNUMBER function (returns true or false). If it returns FALSE, you can convert them to values using the VALUE function and then pasting values. Some reports downloaded from an ERP system have created this problem with me and this is how I fix it
I am one of those ill-talked-about ‘IT guy’ who was asked to Vlookup the generated spreadsheet reports of trade recon with the ones fetched from database. And no I did not take 3 months to reply, but I did face a similar issue. All I had to do to resolve it was convert one of the columns to Text (it was number initially). I think you try ISNUMBER as mentioned by DTM86
'12345 to convert to text paste special multiply by 1 to convert to number either one will work, make sure both are the same
Hard to say without seeing it. DTM86’s idea is not bad. I would try pressing F9 on all the separate terms of the forumla so you can see what you are getting - gradually increasing the amount calculated. Or work the other way, start with INDEX(Range,1,1) do you get the right value, then work forwards Note you can copy the INDEX(…) and paste into goto (F5) and it will take you to the referenced cells. This is useful for sanity checks
i think it’s pretty obvious that the issue is due to number/text conversion there’s really no need to spend a half hour stepping through formulas
i figured it out- it was not formatting- it, like usual, was operator error. (I knew excel was smarter than me) I failed to update the range of my named array after I updated my master matrix and increased its size beyond its previously defined range. My bad. thanks for thos tricks though- I will use them elsewhere!
supersharpshooter Wrote: ------------------------------------------------------- > i think it’s pretty obvious that the issue is due > to number/text conversion > > there’s really no need to spend a half hour > stepping through formulas You know it’s a while since I posted on this forum. You’ve reminded me why I stopped.
boo hoo i’ll tell you what - next time i’ll start with “Did you spell offset correctly?”