Vlookup code ? 2

[baconandeggs]

Hello all

I need to code a VLOOKUP and have named a range.

The problem is using the wizard, that is confusing , my range has two lines the top is numbers i want to put into a range headed TOTAL at the right hand of the form.
the second line is the values they represent.

1 2 3 4 5 6 7
$2 $5 $7 $9 $10 $12

The cell below Total is J10

The range starts at J4 and the named range is called Commission

Has anyone got time to help me out


best regards
jim

 

[bkpchs237]

baconandeggs, (cute handle)
Perhaps the vlookup function help is confusing because of the layout of your data. The vlookup function is for vertical lookup. It looks within a range with data in column form by comparing a lookup value with a value within the first column of the range of data and returns a value a certain number of columns to the right of the first column of the lookup range. For example a typical formula would be =vlookup(f4,$g$2:$k$500,3,false). This would lookup the value in cell F4 in column G and return the value in column I (the third column over), but only for an exact match between the value in F4 with a value in column G. Of course you could name the range $g$2:$k$500 to an appropriate name. This explanation is brief at best and hopefully it helps.
However, by the layout of your data it seems that you are in need of its sister formula, the hlookup function. The hlookup function is for horizontal lookup. The layout of your data suggests that this formula would suit your needs much better. It performs the same function only with the data laid out in rows rather than in columns.
Your data is confusing at best and also incomplete as there are seven numbers across the top and only six beneath. If you provide more specific information as to exactly what you would like I could provide you specific help, but then again, maybe the words above have provided enough assistance.
Hope this helps.

 

[baconandeggs]

bkpchs237
Yes thank you very much for your time and trouble. I have had a little study and must admit i was under the impression that i could put a whole range of data say =HLOOKUP(A1:G1,RANGE,2)
And be able to copy down .. that doesn't work
=HLOOKUP(A1,RANGE,2) copied over cell to cell will only give me one line and it doesn't seem to me to be always right, as i assigned a number say 1 to have the value of £1.oo but it wasn't always accurate .
I shall stick to the tried and trusted I think thanks any way

Best regards
jim

 

[bkpchs237]

baconandeggs,
As I stated before I don't exactly know what you are trying to accomplish, but I offer some further information.
If you want to work with ranges of data look into using array formulas. There are a number of ways to use them to come up with results that might not be easy or possible using other formulas.
Also, by your statement that you were trying to use your formula ...(a1:g1... or ...(a1... and copy the formula down the entire column and "it doesn't seem to me to be always right" you may want to look into using absolute cell references: $a$1 or $a1:$g1 this may help in the future.
Well, I'm sure the tried and true methods are fine, but again, I'm sure that myself and others would offer you some further assistance if you offer some specifics here.
Hope this helps.

 

[Dreamboat]

Hey, breakfast, don't give up. If you want, you can send an example file to me. I'll hook you up with what you need to know or a formula you can use.
techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[baconandeggs]

Hey dreamboat
Your saucy.. apologies to all I have tried to get back but have had to wipe my pc and start anew .

Heres what I wanted to do
#10 numbers which are constant and ascending they could be fixed prices, and it would be easier to enter numbers 1 to 10 which represented these ten values using a names range.

so when you sell item 1 @ £10 you enter 1 and so on..then when you need to total them in say cell K11 =Sum(A11:J11) then copy down.
it's purely to save data entry error what do you think
jim

 

[Dreamboat]

So much easier to show you in a file...

People use this feature to create invoices and such....

Part numbers, description, prices on Sheet 2.

Invoice on sheet 1, you type the part number in and the description and price automatically fill in, right?
techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[baconandeggs]

Dreamboat
You can have my email jim@baconaneggs.demon.co.uk
That is exactly the sort of thing but I am not familiar with the 2 page connection I need to get my head around that.

Best regards

Jim

 

[Dreamboat]

Done. Anybody else want the file, feel free to click on my email and request the file called lookup.xls. It even has a lookup list for data validation (pick an employee from a list of employees).
techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com