Hello Experts, I need ur help with this Vlookup Formula

  • Thread starter Thread starter Kinghart
  • Start date Start date
People are generally reluctant to download files from an unknown
source - try to describe your problem here.

Hope this helps.

Pete
 
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.
 
Gary said:
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.

:
-
Click to expand...

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(F3,$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)


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+
 
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
 
Pete_UK;761529 said:
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
Click to expand...

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


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+
 
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
 
Pete_UK;761643 said:
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 <[email protected]
wrote:-
Click to expand...

Actually I'm not trying to match the value i type... If i write 0.3,
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 loo
at the zip fil

+-------------------------------------------------------------------
+-------------------------------------------------------------------
 
Gary said:
Kinghart,

I downloaded your workbook and made changes that appear to accomplis
your
goal. The workbook is available at
http://cid-11d5f03c65c47ceb.skydrive.live.com/browse.aspx/Documents
Hope this helps.

:
-
Click to expand...

Cool... Thanks Gary that did it... almost :-).... just one mor
thing... The category or range (ie. 0.5, 1, 1.5) it goes does to 50 o
more and i want it to descend (ie. starting from 0.5 to ... ) can
change the starting to 0.5 instead of 1.5 as u've shown in the tabl

+-------------------------------------------------------------------
+-------------------------------------------------------------------
 
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
 
Pete_UK;761816 said:
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
Click to expand...

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)


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+
 
You can begin with whatever value you like as long as the column is in
descending order.

GMc
 
Gary said:
You can begin with whatever value you like as long as the column is in
descending order.

GMc

:
-
Click to expand...

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


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+
 
.. I've got the solution I needed from
someone else in another forum.....
Click to expand...

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$19,),0),MATCH(F3,$B$16:$I$16,0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
 
Back
Top