Novice User: Using a "lookup" function after a cal

W

wotonka

Board Regular
Joined
Aug 6, 2002
Messages
142
I have used a calculation in a query to produce a field called "weight" within a table.
Goal #1: I would like to use the initially calculated results in the "weight" field, and look up values from another table. For example, if the calculated weight were 100 pounds, the result of the lookup would return the text "class A". If the weight were 200 pounds, the lookup would return the text "class B".
Goal #2: I would like to return the text "Class B" if the lookup text were 150 pounds (a value not explicitly stated in the lookup table, yet a value that is anything less than 200 = the next explicitly entered table value). In other words, the lookup would act somewhat like the Vlookup command in Excel: Class = Vlookup (Pounds, Sometable,2).

As a "newbie", I would be most grateful for any assistance that may be offered. Thanks so much in advance.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
It always helps to know how you are going to use this Lookup value. Is this something that you just want to let a user of the system see when they are using a form, or do you want to put this value into a table, or use it to help sort or report on these weights? How you are going to use this will help us help you.
 
Upvote 0
Thank you for your reply. I intend to use a report to display the "Class" information (looked up information) in a report. The Weight information will be used in a query which will sum up all of the weights associated with a given product (not stated in the original request for help). That information will also appear in a report. The reports will look something like:
REPORT 1 - "CLASS" INFORMATION
PRODUCT WEIGHT CLASS
Product 1 100 A
Product 2 150 B
Product 3 200 B

REPORT 2 - "SUM OF WEIGHTS" INFORMATION
CATEGORY TOTAL WEIGHT
NEW 1200
USED 2000

Thanks very much for your willingness to help !
 
Upvote 0
Because you will be using this "class" information to help sort, and then report on, the best place to put this "class" information is in a query of your data. This way, the query can be used to feed into other queries that you can use to report on. I have gotten the idea you know how your "classes" are defined, that is within what ranges of weight for each class. So class A is from 0 to 100 pounds, class B is 101 to 200 pounds, class C from 201 to 300, and (my assumption for illustrative purposes only!) class D from 301 pounds and above.
So, the "formula" would look like this inside a query:
IIf(Weight<=100, "ClassA",IIf(Weight<=200, "ClassB", IIf(Weight<=300, "ClassC", "ClassD")))
HTH,
 
Upvote 0
you could also modify your lookup table to include a min and max weight so that instead of using nested iif's you could just say

WHERE weight between min_wgt and max_wgt

hth,
Giacomo
 
Upvote 0
Thank you for your reply. I believe I can use nested "ifs", but I am curious about the WHERE statement. Does that statement have to be used in a SQL statement, or can it appear as a criteria within a query?
Thanks
 
Upvote 0
The criteria within a query IS part of a SQL statement. A SQL (Structured Query Language) statement is the query. The criteria rows that you see in the graphical display of a SQL statement is just part of the whole SQL statement.
 
Upvote 0
Ok,
Thanks very much to all who have replied. I will give these ideas a try. As a newbie, overcoming these hurdles is a huge help (since there will be many more newbie hurdles to come). MUCH THANKS !!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
VLOOKUP formula using the TEXT function as the "lookup value"
Replies
1
Views
763
awoohaw
awoohaw
A
  • Question
Calculating the MWRR using the (X)IRR Function
Replies
1
Views
314
Avengerooo
A
E
  • Question
Return most recent data - multiple criteria
Replies
0
Views
186
Exceloway
E
F
  • Question
VBA - Rename column header of results column when doing a lookup
Replies
2
Views
435
floggingmolly
F
JeffGrant
  • Solved
XLookup not returning values using hh:mm format as lookup
Replies
1
Views
458
JeffGrant
JeffGrant

Forum statistics

Threads
1,215,681
Messages
6,126,191
Members
449,298
Latest member
Jest

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top