ExcelBanter - Hello Experts, I need ur help with this Vlookup Formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Hello Experts, I need ur help with this Vlookup Formula (https://www.excelbanter.com/excel-worksheet-functions/211634-hello-experts-i-need-ur-help-vlookup-formula.html)

Kinghart

Hello Experts, I need ur help with this Vlookup Formula

1 Attachment(s)

I'm attaching a sample worksheet.. pls take a look and help me.

Pete_UK

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

Gary Mc

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

Kinghart

Quote:

Originally Posted by Gary Mc (Post 761086)

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

Quote:

Originally Posted by Kinghart (Post 761097)

BTW. It didn't work Gary.. but thanks

Pete_UK

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

Kinghart

Quote:

Originally Posted by Pete_UK (Post 761529)

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

Kinghart

Quote:

Originally Posted by Kinghart (Post 761546)

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

Pete_UK

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

Gary Mc

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

Kinghart

Quote:

Originally Posted by Pete_UK (Post 761643)

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

Kinghart

Quote:

Originally Posted by Gary Mc (Post 761735)

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

Pete_UK

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 -

Kinghart

Quote:

Originally Posted by Pete_UK (Post 761816)

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)

Gary Mc

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

Kinghart

Quote:

Originally Posted by Gary Mc (Post 762396)

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 :-)

Max

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

All times are GMT +1. The time now is 11:37 PM.