Lookup an exact Value with a range

[Devabrata Mullick]

Table - 1 : -
A B C D E
Start_SR End_SR Qnty Party Item
1 1 10 10 A X
2 25 50 26 B Y
3 75 100 26 C Z
4 200 600 401 E XX
5 2156 2550 395 D YY
6 1500 1599 100 F ZZ

Table - 2 : -
SR Party Item
2 =VLOOKUP(H3,B2:E8,4,1)
1100
500 =LOOKUP(H5,B2:E8)
76
1200
2200
In Table-1 I have some data with a range ( Columd A - B) which range billed
out against the party and item...... want to pick up the Party & Item in the
Table-2..... I used these two functions.... but it is working when the both
data is shorted and takes the nearest value....... But i want to the exact
value and randomly........

Pls help.....

Regards,
Devabrata

 

[Ashish Mathur]

Hi,

Instead of 1 in the last arguent, use a 0

=VLOOKUP(H3,B2:E8,4,0)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Devabrata Mullick]

By using this argument the outcomes is "#N/A"

Hi,

Instead of 1 in the last arguent, use a 0

=VLOOKUP(H3,B2:E8,4,0)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Jacob Skaria]

If you are getting #NA there are two possiblities
1. The lookup value in H3 is not in the first column of the array Column B
2. Either the value in H3 or the valee in Col B is having decimals are
formatted to display differently..so it will not be a exact match...

The formula should work. Try out with values manaully entered in to the
cell...

If this post helps click Yes

 

[Devabrata Mullick]

Hi Jacob, Thankx a lot...... But I hope you couldn't fot my problem.... There
is a range (Column B to Column C) and against this range we have the Party
name and Item name..........
We need to pick up the party name and the Item name from that range against
the SR( Table - II)........

 

[Jacob Skaria]

Try the below..

Table1: Row1 with header and data starts from Row2

Col A Col B Col C Col D Col E
Start_SR End_SR Qnty Party Item
1 10 10 A X
25 50 26 B Y
75 100 26 C Z
200 600 401 E XX
2156 2550 395 D YY
1500 1599 100 F ZZ

Table2: in Column H/I/J. Headers in Row 1

Col H Col I Col J
SR Party Item
1598 =formula1 =formula2

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

Formula1 in cell I2 (and copy down as required)
=INDEX($D$1:$D$7,MATCH(1,($A$1:$A$7<=H2)*($B$1:$B$7>=H2)))

Formula2 in cell J2 (and copy down as required)
=INDEX($E$1:$E$7,MATCH(1,($A$1:$A$7<=H2)*($B$1:$B$7>=H2)))


If this post helps click Yes

 

[Devabrata Mullick]

thnkx.... if possiable i can send you the speciment data......

 

[Devabrata Mullick]

mail ID please...

Try the below..

Table1: Row1 with header and data starts from Row2

Col A Col B Col C Col D Col E
Start_SR End_SR Qnty Party Item
1 10 10 A X
25 50 26 B Y
75 100 26 C Z
200 600 401 E XX
2156 2550 395 D YY
1500 1599 100 F ZZ

Table2: in Column H/I/J. Headers in Row 1

Col H Col I Col J
SR Party Item
1598 =formula1 =formula2

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

Formula1 in cell I2 (and copy down as required)
=INDEX($D$1:$D$7,MATCH(1,($A$1:$A$7<=H2)*($B$1:$B$7>=H2)))

Formula2 in cell J2 (and copy down as required)
=INDEX($E$1:$E$7,MATCH(1,($A$1:$A$7<=H2)*($B$1:$B$7>=H2)))


If this post helps click Yes

 

[Devabrata Mullick]

hi Jacob,
This is not working properly....... It picks up the nearst value or the last
value of the range when shorting ....