Query on an Excel formula

Microsoft Excel 2002
May 18, 2010 at 08:02:51
Specs: Windows Vista
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?

See More: Query on an Excel formula

Report •


#1
May 18, 2010 at 08:40:35
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

http://www.skeptic.com/


Report •

#2
May 18, 2010 at 08:59:52
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.

Report •

#3
May 18, 2010 at 10:13:41
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:

Relative And Absolute References In Formulas


Report •

Related Solutions

#4
May 18, 2010 at 10:42:01
re: This will look at Sheet 2 cell A1 and attempt to find a match on Sheet 1 in the cell range A1 thru B20

Just to clarify the above statement:

Quoting the Help file on VLOOKUP, the function "searches for a value in the leftmost column 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 thru B20.


Report •

#5
May 18, 2010 at 12:04:09
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.


Report •

#6
May 18, 2010 at 12:25:32
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 typically the 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.


Report •


Ask Question