What type of "lookup"

[Kat]

I have a spreadsheet with 5 tabs. Each sheet in the tab has 4 columns (A, B,
C, D) Sheet 1, Sheet 2, Sheet 3, Sheet 4 and Sheet 5.

Each sheet pertains to different customers except, Sheet 1 is my reporting
sheet to find the differences.

On each sheet:
Column A - Text (part numbers, Alpha sorted)
Column B - Part description
Column C - UPC #
Column D - Price per 100

Sheet 2 has the most info it in. All part numbers (columnA) listed in
Alphabetic order from Cell A2 - A4675, it is the most extensive price sheet.

Sheets 3, 4 & 5 have anywhere from 309 - 485 rows of part numbers.

Sheet 1 has all part numbers listed alphabetic order in Column A starting in
Cell A2

I want to compare the price each customer gets (column d) and report that on
sheet 1. Here is the formula I'm trying to use in sheet 1, B2:
=LOOKUP(A2,Sheet2!$A$2:$A$309,Sheet2!$D$2:$D$309)
The formula changes for each row to "lookup(A3...", "lookup(A4..." etc.

My problem is that if the formula finds a price it reports it fine but the
next row doesn't have a price and it repeats the price from the row above
until it finds another difference. Any suggestions to a better formula or
what might be wrong with mine?

Any help is greatly appreicated.

Thanks in Advance

Kat

 

[Glenn]

I have a spreadsheet with 5 tabs. Each sheet in the tab has 4 columns (A, B,
C, D) Sheet 1, Sheet 2, Sheet 3, Sheet 4 and Sheet 5.

Each sheet pertains to different customers except, Sheet 1 is my reporting
sheet to find the differences.

On each sheet:
Column A - Text (part numbers, Alpha sorted)
Column B - Part description
Column C - UPC #
Column D - Price per 100

Sheet 2 has the most info it in. All part numbers (columnA) listed in
Alphabetic order from Cell A2 - A4675, it is the most extensive price sheet.

Sheets 3, 4 & 5 have anywhere from 309 - 485 rows of part numbers.

Sheet 1 has all part numbers listed alphabetic order in Column A starting in
Cell A2

I want to compare the price each customer gets (column d) and report that on
sheet 1. Here is the formula I'm trying to use in sheet 1, B2:
=LOOKUP(A2,Sheet2!$A$2:$A$309,Sheet2!$D$2:$D$309)
The formula changes for each row to "lookup(A3...", "lookup(A4..." etc.

My problem is that if the formula finds a price it reports it fine but the
next row doesn't have a price and it repeats the price from the row above
until it finds another difference. Any suggestions to a better formula or
what might be wrong with mine?

Any help is greatly appreicated.

Thanks in Advance

Kat

Try this:

=VLOOKUP(A2,Sheet2!$A$2:$D$309,4,FALSE)

 

[Kat]

Try this:

=VLOOKUP(A2,Sheet2!$A$2:$D$309,4,FALSE)

Looks like this will work, THANKS!! =) I have a few questions though. I
like to understand what the formula means. I understand the beginning up
until the ,4,false how does that make the answer show up? why a number 4
instead of 1,2 or 3?

thanks

 

[Glenn]

Looks like this will work, THANKS!! =) I have a few questions though. I
like to understand what the formula means. I understand the beginning up
until the ,4,false how does that make the answer show up? why a number 4
instead of 1,2 or 3?

thanks

4 refers to the column number in the table from which you want to return a
value. False is a setting that requires an exact match.

If the help file doesn't explain it well enough, try here:

http://www.contextures.com/xlFunctions02.html