-3

I have two sheets in Excel i.e. sheet1 and sheet2.

In sheet1's column A there are account numbers of the customer and in column B there are names of the customers.

Now I want that in column A of sheet2 when I type any account number of the customer from sheet1's column A, it should show the name of the customer in column B which is written in sheet1's column b.

For example I am attaching a snapshot of both the sheets i.e. sheet1 and sheet2.

In the following image only the account no the customers in column A (in sheet2) will be typed by me and the name of the customers should automatically be appeared in column B in the front of the customer's account.

enter image description here

Improve this question

3 Answers 3

Reset to default
1

There are two different ways I would do this: VLOOKUP or INDEX/MATCH.

In sheet 2, cell B2, use formula 

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

Drag this formula down as far as you require, where:

  • A2 is the first account code in sheet 2 you wish to look up
  • $A$2 is the start location in sheet 1 with the row column indicators preceded by a dollar sign (the dollar signs mean this location is fixed, so will not change when copying)
  • Sheet1! is the instruction to tell Excel you are looking at a different sheet
  • $B$11 is the last item in the list and the column in which the data to be looked up occurs.
  • 2 is the column count of the data you want to return (in this case A is column 1 and B is column 2)
  • false is the indicator to say that you want an exact match from your account number. If the number doesn't exist, the error #N/A appears, otherwise you get the account name.

Alternatively, use a combination of the INDEX and MATCH functions: Again in B2 type

=INDEX(Sheet1!B:B,MATCH(Sheet2!A2,Sheet1!A:A,0))

Drag down as many cells as you need

This finds the row number in sheet 1 which corresponds to the contents of A2 in sheet 2, then returns the name located in column B in the same row.

The advantage of INDEX/MATCH is that if the data you want to look up is to the left of the data you are searching for, this will still work, whereas VLOOKUP can only look from left to right.

Improve this answer
0

Is this not a simple vlookup? In Sheet2 =vlookup([cell reference to account number], [lookup in this array in this range in sheet1], [return this column in range], [true/false for match type]).

VLOOKUP function

Improve this answer
-1

lookup function can be used here. there are two types of lookup functions vlookup and hlookup.

with the help of LOOKUP function this problem can be solved.

Improve this answer
1
  • 1
    While this may answer the question, it would be a better answer if you could provide some explanation why it does so.
    – DavidPostill ♦
    Dec 15, 2014 at 18:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .