Hello,
I'm having trouble with vlookups and index/match in a table. I explained the vlookup issue in this post "[h=2]VLOOKUP Only Returning Some Values otherwise #N/A but Index/Match Works".[/h]
It seemed like index/match would work but one row out of a a couple thousand did not work. I'm very familiar with vlookups but tables and the index/match functions are new to me. I have one list of inventory items I'm trying to look to and pull descriptions into a table. There are two columns in the table where descriptions are being pulled. For the same alpha numeric inventory item, C-1621S a vlookup works but an index/match does not.
The C-1621S is listed in two columns. I have copied and pasted to ensure both columns have the same data. Since it's alpha numeric, I'm assuming it cannot be a text versus number formatting issue? However, on both columns, I used Text to Columns - Delimited - no delimiter - General formatting. The vlookup column did not change in appearance. The index/match column changed all formulas to text strings by adding the '. I can't remember this happening before. Does this have to do with the table format somehow?
I've seen if I add rows at the bottom, one cell automatically gets highlighted a color I used at the top of the table even though I do not want it highlighted so I have to clear the color. So maybe the index/match column has some sort of original formatting? I'm not sure how to remove previous formatting like the highlighting example?
I'm using a named range called FT_Inv for the vlookup. The sheet containing the inventory items is called "FT All Inv".
The vlookup formula is: =VLOOKUP(B10,FT_Inv,7,FALSE)
The index/match formula is: =INDEX('FT All Inv'!J:J,MATCH([@[Full_Comp_Part]],'FT All Inv'!D:D,0))
Does anyone know why the columns seem to be acting differently? Thanks in advance!
I'm having trouble with vlookups and index/match in a table. I explained the vlookup issue in this post "[h=2]VLOOKUP Only Returning Some Values otherwise #N/A but Index/Match Works".[/h]
It seemed like index/match would work but one row out of a a couple thousand did not work. I'm very familiar with vlookups but tables and the index/match functions are new to me. I have one list of inventory items I'm trying to look to and pull descriptions into a table. There are two columns in the table where descriptions are being pulled. For the same alpha numeric inventory item, C-1621S a vlookup works but an index/match does not.
The C-1621S is listed in two columns. I have copied and pasted to ensure both columns have the same data. Since it's alpha numeric, I'm assuming it cannot be a text versus number formatting issue? However, on both columns, I used Text to Columns - Delimited - no delimiter - General formatting. The vlookup column did not change in appearance. The index/match column changed all formulas to text strings by adding the '. I can't remember this happening before. Does this have to do with the table format somehow?
I've seen if I add rows at the bottom, one cell automatically gets highlighted a color I used at the top of the table even though I do not want it highlighted so I have to clear the color. So maybe the index/match column has some sort of original formatting? I'm not sure how to remove previous formatting like the highlighting example?
I'm using a named range called FT_Inv for the vlookup. The sheet containing the inventory items is called "FT All Inv".
The vlookup formula is: =VLOOKUP(B10,FT_Inv,7,FALSE)
The index/match formula is: =INDEX('FT All Inv'!J:J,MATCH([@[Full_Comp_Part]],'FT All Inv'!D:D,0))
Does anyone know why the columns seem to be acting differently? Thanks in advance!
