Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 21
Default Hello Experts, I need ur help with this Vlookup Formula

I'm attaching a sample worksheet.. pls take a look and help me.
Attached Files
File Type: zip Copy of freight.zip (13.3 KB, 285 views)

Last edited by Kinghart : November 25th 08 at 10:13 PM
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 17
Default 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   Report Post  
Junior Member
 
Posts: 21
Default

Quote:
Originally Posted by Gary Mc View Post
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
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)
  #5   Report Post  
Junior Member
 
Posts: 21
Default

Quote:
Originally Posted by Kinghart View Post
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)
BTW. It didn't work Gary.. but thanks


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Junior Member
 
Posts: 21
Default

Quote:
Originally Posted by Pete_UK View Post
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
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
  #8   Report Post  
Junior Member
 
Posts: 21
Default

Quote:
Originally Posted by Kinghart View Post
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
Hey Pete... There are some other problems too... Actually what I want to do is that when I type 0.4, it should take the amounts in the row 0 - 0.5...
when i type 1.21, it should take the amounts in the row 1.00 - 1.5
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 17
Default 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   Report Post  
Junior Member
 
Posts: 21
Default

Quote:
Originally Posted by Pete_UK View Post
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
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
  #12   Report Post  
Junior Member
 
Posts: 21
Default

Quote:
Originally Posted by Gary Mc View Post
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
  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Junior Member
 
Posts: 21
Default

Quote:
Originally Posted by Pete_UK View Post
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 -
It is working as it is now.... but when i sort the coumn A (ie. 0.5, 1, 1.5) it doesn't work.... specially for values below 0.5 (ie. 0.3, 0.4)
  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 17
Default 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   Report Post  
Junior Member
 
Posts: 21
Default

Quote:
Originally Posted by Gary Mc View Post
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
Thanks 4 everythings guys... I've got the solution I needed from someone else in another forum..... u tried ur best but i guess i cudn't tell u my problem good enough.... anyways thanks and see u later :-)
  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula stopped working - need experts help! Terri[_2_] New Users to Excel 5 July 13th 08 11:00 PM
Challenge experts will LOVE!!, Can you get the formula?? Drummy Excel Discussion (Misc queries) 0 May 25th 06 07:17 AM
for the experts Herman Excel Discussion (Misc queries) 2 October 28th 05 11:59 PM
again for the experts Herman Excel Discussion (Misc queries) 0 October 28th 05 04:13 PM
another for the experts Wazooli Excel Discussion (Misc queries) 3 March 26th 05 08:21 AM


All times are GMT +1. The time now is 11:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"