# Lookup type question

##### New Member
Hi

Onto the next problem

I am currently trying to add in a lookup type query where if the figure is between 2 values then a second column will autofill with the corrospding figure.

so

Pts - Column1 - Column2

200 to 300 - 10 - 20
301 to 400 - 15 - 25
401 to 500 etc

cheers

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### VicRauch

##### Well-known Member
Don't quite understand what you are asking for here. You are talking about two columns, and it looks like column 1 has a pair of values of 200 to 300, and then column 2 should populate with 10-20? What is the algarythm that dictates the 10-20 for 200 to 300? and then 301-400 gives 15-25? Could you try to explain this a little bit better?
Thanks,

#### MyBoo

##### Board Regular
I was going to suggest a simple Select Case statement but then realized that you want column2 to be a varying number, based on the varying number of column1. Kind of difficult to provide assistance when one doesn't have all the pertinent information.

Does the user update column1?

I would suggest you use a two column combobox and use code to place combobox.column(1) into your column2 field.

##### New Member
Sorry was a bit late when i wrote it, made a bit more sense to me at the time

Will try and clear up a bit better.

From a questionaire an individual ends up with a points total. this points total then relates to a point on a grade structure, so different point bandings will equal a different point on the structure.

The 2 columns represent 2 different schemes being used (so in all reality i could use the same formula/ expression and just change the point boundries).

e.g

person A scores a number of points, on the scheme being used anyone who scores between 200 and 300 would end up in grade 3 on scheme 1 and in grade 5 in scheme 2, anyone who scores between 301 and 400 would end up in grade 4 on scheme 1, and grade 7 on scheme 2 etc.

Hope that makes it a bit clearer.

#### VicRauch

##### Well-known Member

What is the formula that produces grade 3 in scheme 1 if between 200 and 300? What is the formula that produces grade 5 in scheme 2 if between 200 and 300?
Or is there a table somewhere that just states these items. If it is a table, please show us some of the table. If it is a formula, please show us the formula.
Thanks,

##### New Member
havnt got to the formula stage on this one yet (this was partly what i was unsure on). I wasnt sure whether a Lookup type query was the best way, or if a series of IF Statements are required.

At the moment i have my main table which contains the points total and a column for scheme 1 and a column for scheme 2. I currently have a table for each scheme which contains an ID and the list of grades that make up the scheme.

sorry if im not explaing this very well, this is the first time ive tried this in access. Nearest comparator i could give would be the Vlookup in excel, but am not sure how this would be expressed in access

#### VicRauch

##### Well-known Member

At the moment i have my main table which contains the points total and a column for scheme 1 and a column for scheme 2. I currently have a table for each scheme which contains an ID and the list of grades that make up the scheme.
Please, please, please, let us see what you have. You say you have a main table which contains points total in a column for scheme1 and another for scheme 2, then you have a table for each scheme which contains an ID and the list ofgrades that make up the scheme. We have NO idea what is in these tables. Show us so we can help.

##### New Member
Hi Vic

Sorry for driving you the wall with this one. Seen the answer in one of the posts today http://www.mrexcel.com/board2/viewtopic.php?t=239203 (IIF statement)

for future ref when you say see the tables im assuming we can not upload. is there a certain layout that is used on the boards for this?

cheers for the help (although someone else made the same question much easier to understand )

#### VicRauch

##### Well-known Member
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
This is how the tables where shown to the board in the thread you referenced. An even better way to show this would be:
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....TotalWeight
NEW..............1200
USED.............2000
By spacing out the column titles, showing the titles in Upper/Lower case with no blanks in column names, and spacing out the sample data until it shows under the correct column name, it does make it easier to see. The web pages that we are viewing this stuff on will only allow 1 blank space between words, unless you code the " " in the source. Therefore, use periods to space the data and column headings out.
BTW, this sample is really not a good one in that in report 1, Category is a letter ("A", "B") and in report 2 Category is "NEW" or "USED"

#### SydneyGeek

##### MrExcel MVP
Access doesn't have a VLOOKUP equivalent built in.

A guy called Allen Browne has created ELOOKUP (Extended Lookup), a custom function for Access with similar functionality. Check it out

Denis

Replies
3
Views
1K
Replies
9
Views
291
Replies
5
Views
859
Replies
7
Views
319
Replies
1
Views
156

1,136,267
Messages
5,674,730
Members
419,523
Latest member
Urnovio

### 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.

### Which adblocker are you using?

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

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