I have created a database on Worksheet 1, column A showing Ref. No. of customer and Column B showing name & address. On Worksheet 2 when I enter the Ref. Number is there a formula which can bring forward the name and address from the database on Worksheet 1?

Look at the =VLOOKUP() function. On sheet 2 in cell A1 enter your Ref. No.

On sheet 2 in cell B1 enter this formula:

=VLOOKUP(A1,Sheet1!A1:B20,2,)

This will look at Sheet 2 cell A1 and attempt to find a match

on Sheet 1 in the cell range A1 thru B20 and will select the 2nd column (Column B, Name etc) to copy back over to Sheet 2.MIKE

Nearly there. When copying formula down 1 row is added each tim., Row 2 is =VLOOKUP(A2,Sheet1!A2:B21,2,) Row 3 is =VLOOKUP(A3,Sheet1!A3:B22,2,) Therefore not looking through entire table.

Read this and see if you can determine what needs to be changed in Mike's formula. If not, come on back and we'll gve you some hints:

re: This will look at Sheet 2 cell A1 and attempt to find a match on Sheet 1 in the cell range A1 thru B20Just to clarify the above statement:

Quoting the Help file on VLOOKUP, the function "searches for a value in the

leftmostcolumn of a table."Therefore, Mike's VLOOKUP formula will attempt to find a match on Sheet 1 in the cell range A1 thru

A20, not A1 thruB20.

I now have it sorted by reading the 'Absolute References in Formulas' I put a $ in front of the row number so that it does not increment the row. Thanks for your help. It will save a lot of typing time.

Good for you - sort of. :-) Typically, when using VLOOKUP, Absolute referencing is used for both the Row number and the Column number.

The reason for this is that

typicallythe lookup table is "static" meaning that you will always be looking up data in the leftmost column and then choosing which column to extract data from.By placing the dollar sign before both the Column and Row numbers, you can drag the formula up, down and across as well as copy and paste it and always refer to the same table.

In your case, just using Absolute referencing for the Row will certainly work, it's just more common to lock down the entire table.

Ask Your Question

Weekly Poll

Do you think Samsung's Bixby will compete well against other phone AI systems?

Discuss in The Lounge

Poll History