VLOOKUP in multiple columns and returning one value

[CDNWolf]

Unfortunately I could not find a reference to this problem in the forum, any help would be greatly appreciated...


I am looking to use vlookup in order to look up competitor part numbers and return the value of my part numbers.


I have a chart outlining all the information...

Column A: my part number
Column B: Competitor 1 number
Column C: Competitor 2 number
Column D: Competitor 3 number

ie:
CB-0404 FR-3214 DB-0123 PP9876

For the above sample part numbers FR-3214 or DB-0123 or PP9876 should return a value of CB-0404 (my part number)...


It seems like a straight forward formula but I am getting a value of #N/A using the following formulas...

=VLOOKUP(A2,Referances!$A$4:$J$12,1,FALSE)
=VLOOKUP(A3,parts,1,FALSE) - Parts is a named defined range


Thanks in advance for your assistance

 

[CDNWolf]

Thanks Andrew, I tried that but didn't do the A3 so it failed...

Just tried the A$3 and it works perfectly...

Thanks again for your help