200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Sir,
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")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.