Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Hello Experts, I need ur help with this Vlookup Formula
I'm attaching a sample worksheet.. pls take a look and help me.
Last edited by Kinghart : November 25th 08 at 09:13 PM |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hello Experts, I need ur help with this Vlookup Formula
People are generally reluctant to download files from an unknown
source - try to describe your problem here. Hope this helps. Pete On Nov 25, 8:16*pm, Kinghart wrote: I'm attaching a sample worksheet.. pls take a look and help me. +-------------------------------------------------------------------+ |Filename: Copy of freight.zip * * * * * * * * * * * * * * * * * * *| |Download:http://www.excelbanter.com/attachment.php?attachmentid=126| +-------------------------------------------------------------------+ -- Kinghart |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hello Experts, I need ur help with this Vlookup Formula
Reverse the order of your table (i.e. switch row 17 & 19) so 1.5 is in A17
and .5 is in A19 then change the third argument of your first MATCH from 0 to -1. That should do it. "Kinghart" wrote: I'm attaching a sample worksheet.. pls take a look and help me. +-------------------------------------------------------------------+ |Filename: Copy of freight.zip | |Download: http://www.excelbanter.com/attachment.php?attachmentid=126| +-------------------------------------------------------------------+ -- Kinghart |
#4
|
|||
|
|||
Quote:
I have a table like this < a b c d e 0-0.5 25 26 27 38 48 0.5-1.00 32 34 39 42 49 1.00-1.50 36 39 41 45 52 1.51-2.00 39 43 49 52 56 I want C7 to show 41 when i type 1.10 or 1.30 or 1.49 in B7 I'm using a formula like this: =INDEX($B$17:$I$19,MATCH(B3,$A$17:$A$19,0),MATCH(F 3,$B$16:$I$16,0)) But that formula only works when i write 1.00 and 1.50 in B7... (not 1.10 or 1.20 or 1.23) |
#5
|
|||
|
|||
Quote:
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hello Experts, I need ur help with this Vlookup Formula
The formula doesn't tie up with what you have written - the formula is
looking to match B3 with cells in column A and F3 with cells in row 16, whereas you mention B7 (only) in your description. Also, the formula expects data up to column I, but only in 3 rows. However, I have set up this table so that it occupies A16 to F20: a b c d e 0 25 26 27 38 48 0.5 32 34 39 42 49 1.0 36 39 41 45 52 1.51 39 43 49 52 56 Then I used A7 to enter the values a, b, c, d or e (equivalent to your F3 ?) and B7 to enter numbers, and in C7 I put this formula: =INDEX($B$17:$F$20,MATCH(B7,$A$17:$A$20),MATCH(A7, $B$16:$F$16,0)) It returns the corresponding value from the table, depending on A7 and B7. You might like to extend the ranges to suit your data. Hope this helps. Pete On Nov 26, 7:37*pm, Kinghart wrote: OK Pete.... I have a table like this < * * * * * * * *a * * *b * * * c * * * d * * * *e * 0-0.5 * * * * *25 * * 26 * * 27 * * *38 * * *48 0.5-1.00 * * *32 * * 34 * * 39 * * *42 * * 49 1.00-1.50 * *36 * * *39 * *41 * * *45 * * 52 1.51-2.00 * *39 * * *43 * *49 * * *52 * * 56 I want C7 to show 41 when i type 1.10 or 1.30 or 1.49 in B7 I'm using a formula like this: =INDEX($B$17:$I$19,MATCH(B3,$A$17:$A$19,0),MATCH(F 3,$B$16:$I$16,0)) But that formula only works when i write 1.00 and 1.50 in B7... (not 1.10 or 1.20 or 1.23) +-------------------------------------------------------------------+ +-------------------------------------------------------------------+ -- Kinghart |
#7
|
|||
|
|||
Quote:
|
#8
|
|||
|
|||
Quote:
when i type 1.21, it should take the amounts in the row 1.00 - 1.5 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hello Experts, I need ur help with this Vlookup Formula
It works okay for me.
Are you sure you have the data table in exactly the same cells that I used? The first match in the formula is looking at cells A17 to A20 to try to match the number, and the second match is looking at B16 to F16 (or you might have I16) to try to match the letter, and the numbers you want to return are in B17 to F20 (or I20). Hope this helps. Pete On Nov 27, 5:39*am, Kinghart wrote: Hi Pete... thanks Its almost working but if i enter a value less than 0.5, it doesn't work.... what should i do now +-------------------------------------------------------------------+ +-------------------------------------------------------------------+ -- Kinghart |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hello Experts, I need ur help with this Vlookup Formula
Kinghart,
I downloaded your workbook and made changes that appear to accomplish your goal. The workbook is available at http://cid-11d5f03c65c47ceb.skydrive...aspx/Documents Hope this helps. "Kinghart" wrote: I'm attaching a sample worksheet.. pls take a look and help me. +-------------------------------------------------------------------+ |Filename: Copy of freight.zip | |Download: http://www.excelbanter.com/attachment.php?attachmentid=126| +-------------------------------------------------------------------+ -- Kinghart |
#11
|
|||
|
|||
Quote:
if i write 1.45, i want it to take the row 1.00-1.50 sorry I'm not very good in explaining the problem.... pls take a look at the zip file |
#12
|
|||
|
|||
Quote:
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hello Experts, I need ur help with this Vlookup Formula
The formula will do that - it is not looking for exact matches.
Your zip file contains an .xlsx file, which is no use when you have XL2000, as I have. Pete On Nov 27, 7:47*pm, Kinghart wrote: Actually I'm not trying to match the value i type... If i write 0.3, i want it to take the row 0-0.5 if i write 1.45, i want it to take the row 1.00-1.50 sorry I'm not very good in explaining the problem.... pls take a look at the zip file +-------------------------------------------------------------------+ +-------------------------------------------------------------------+ -- Kinghart- Hide quoted text - - Show quoted text - |
#14
|
|||
|
|||
Quote:
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hello Experts, I need ur help with this Vlookup Formula
You can begin with whatever value you like as long as the column is in
descending order. GMc "Kinghart" wrote: Gary Mc;761735 Wrote: Kinghart, I downloaded your workbook and made changes that appear to accomplish your goal. The workbook is available at http://cid-11d5f03c65c47ceb.skydrive...aspx/Documents Hope this helps. "Kinghart" wrote: - I'm attaching a sample worksheet.. pls take a look and help me. +-------------------------------------------------------------------+ |Filename: Copy of freight.zip | |Download: http://www.excelbanter.com/attachment.php?attachmentid=126| +-------------------------------------------------------------------+ -- Kinghart - Cool... Thanks Gary that did it... almost :-).... just one more thing... The category or range (ie. 0.5, 1, 1.5) it goes does to 50 or more and i want it to descend (ie. starting from 0.5 to ... ) can i change the starting to 0.5 instead of 1.5 as u've shown in the table +-------------------------------------------------------------------+ +-------------------------------------------------------------------+ -- Kinghart |
#16
|
|||
|
|||
Quote:
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hello Experts, I need ur help with this Vlookup Formula
.. I've got the solution I needed from
someone else in another forum..... Looks like this worked for the OP in that other forum: http://www.excelforum.com/showthread.php?p=2004944 Quote: first I want to match the text in f3 from the table next I want to match the value in b3 and display the value in c7 I'm using a formula like this: INDEX($B$17:$I$19,MATCH(B3,$A$17:$A$19,-1),MATCH(F3,$B$16:$I$16,0)) Try in C7, normal ENTER: =INDEX($B$17:$I$19,MATCH(TRUE,INDEX(B3<=$A$17:$A$1 9,),0),MATCH(F3,$B$16:$I$16,0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula stopped working - need experts help! | New Users to Excel | |||
Challenge experts will LOVE!!, Can you get the formula?? | Excel Discussion (Misc queries) | |||
for the experts | Excel Discussion (Misc queries) | |||
again for the experts | Excel Discussion (Misc queries) | |||
another for the experts | Excel Discussion (Misc queries) |