Index match problems

[Don O]

I'm trying to use the index/match formula to look up or return related
values. The key data is a 6 digit "VIN" number which sometimes begins
with 0. I enter the VIN on sheet1 A1 then other data that relates to
that VIN in B1,C1, etc. I then enter a new VIN on A2 and related data
on B2,C2 etc. down the line. On sheet2, I have an index match formula
to call the related data based on the matching VIN number. My formula
works for almost all vin numbers. But some return #N/A. I don't see
why or how to fix it. I've tried different formating.
Thanks for your help.
Don O

 

[Frank Kabel]

Hi
are these numbers the ones with a leading zero?. If yes try to format
the entire column as 'Text' and also search for a string. Maybe post
your exact formula as well

 

[Don O]

Greetings Frank,
No, they are not necessarily those starting with zero. But because
some do I prefer to have all formated as text. This seems to be my
problem in copying formulas and ending up with formulas no data. I'll
speak to that in the other thread.
Anyway, here's the formula. I prefer the feild to be blank if the key
is not entered - hence the IF part.
=IF($A18>0,INDEX('BUY DTA'!$A$2:$G$1000,MATCH($A18,'BUY
DTA'!$G$2:$G$1000,),MATCH("MAKE",'BUY DTA'!$A$2:$G$2,)),"")
The items it chooses not to select seem randomly placed in G column
(labeled "vin")in the buy dta sheet. Entering the same vin anywhere in
the sale dta sheet always gives the same result. So it seems to me the
problem is in the buy dta sheet.
Thanks for helping Frank
Don O