A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

What type of "lookup"



 
 
Thread Tools Display Modes
  #1  
Old May 7th, 2010, 07:49 PM posted to microsoft.public.excel.worksheet.functions
Kat[_6_]
external usenet poster
 
Posts: 2
Default What type of "lookup"

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





  #2  
Old May 7th, 2010, 08:11 PM posted to microsoft.public.excel.worksheet.functions
Glenn[_6_]
external usenet poster
 
Posts: 1,245
Default What type of "lookup"

Kat wrote:
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)
  #3  
Old May 7th, 2010, 08:50 PM posted to microsoft.public.excel.worksheet.functions
Kat[_6_]
external usenet poster
 
Posts: 2
Default What type of "lookup"


"Glenn" wrote in message
...
Kat wrote:
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)



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  
Old May 7th, 2010, 09:12 PM posted to microsoft.public.excel.worksheet.functions
Glenn[_6_]
external usenet poster
 
Posts: 1,245
Default What type of "lookup"

Kat wrote:
"Glenn" wrote in message
...
Kat wrote:
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)



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 he

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




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.