to connect names with their registration ID's, which is alpha numerical



  In Excel, in Sheet 1 named as supplier details I have stored names, in text format, in col A, and in col B I have entered their registration details, which is alpha numerical format. Now in sheet 2, named as Purchase details, when I type any name, which is entered in sheet 1 (named supplier details), in col a of Purchase details, the corresponding registration details should come in col b, how to achieve this.




You might use the VLOOKUP() function for this, e.g.

= VLOOKUP($A2,'Sheet1'!$A:$B, 2)

Interpreted, the formula says "Look for the value in $A2 anywhere in the first column of the range 'Sheet1'$A:$B and return the value in column 2 from the row where you found it. Excel will drop the apostrophes embracing the sheet name if that name doesn't contain any spaces (as is the case in the above example).

The formula will return a N/A error if the value isn't found. To catch the error you might embed the formula in an IFERROR() function, like this.

=IFERROR(VLOOKUP($A2,'Sheet1'!$A:$B, 2),"No Match")
You can replace the "no match" string with any string you like including a null string ("").

Looking in all the 1048576 rows of the range $A:$B takes a little time and will slow down your worksheet if there are many evaluations to do. Therefore it would be better practice to specify a named range. If you feel one is too much trouble and the other too slow you might limit the range to something like 'Sheet'!$A$2:$B$1000.



its not working, can i add an excel sheet for claarification
FDT Aug 30, '17 at 5:15 am
its not working, can i attach an excel sheet for claarification
FDT Aug 30, '17 at 5:19 am
It worked for me. Please attach the workbook to your original question. Make sure you take out personal things.
Variatus (rep: 813) Aug 30, '17 at 6:35 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login