Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP
Hi Everybody,
I am making a billing system which will pick product prices automatically from a price list of products. My prices should change automatically according to latest price. I have products like this column A column B column C Product price as 1.11.07 price as on 10.11.07 Milk 12.50 14.00 Chocolate 40.00 50.00 Ice cream 20.00 30.00 Soda 10.00 12.00 now I have created a dynamic named range called pricing which increases a column with every new price. Now my question is that is there any way that Vlookup function automatically change the column index number to the last column of the range (pricing)? in this case from 2(col b) to 3 (col c) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP
One way
Assuming source table in Sheet1, cols A to C (extendable across) Try something like this in another sheet: =VLOOKUP(A1,Sheet1!A:Z,COUNTA(Sheet1!$1:$1),0) where the col index would be determined by the number of filled cells in row1, which number corresponds to the rightmost col number. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Arup C" wrote: Hi Everybody, I am making a billing system which will pick product prices automatically from a price list of products. My prices should change automatically according to latest price. I have products like this column A column B column C Product price as 1.11.07 price as on 10.11.07 Milk 12.50 14.00 Chocolate 40.00 50.00 Ice cream 20.00 30.00 Soda 10.00 12.00 now I have created a dynamic named range called pricing which increases a column with every new price. Now my question is that is there any way that Vlookup function automatically change the column index number to the last column of the range (pricing)? in this case from 2(col b) to 3 (col c) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP
=VLOOKUP(A1,pricing,COLUMNS(pricing),FALSE)
columns(pricing) returns you the amount of columns of the range you enter. this should work, if pricing is a global range. if pricing is a sheet range you need to enter: "sheet1!pricing" instead of just "pricing" where you change sheet1 to your sheet. hth Carlo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |