| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup an exact Value with a range
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup an exact Value with a range
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" <Devabrata wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup an exact Value with a range
By using this argument the outcomes is "#N/A"
"Ashish Mathur" wrote: 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" <Devabrata wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup an exact Value with a range
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 --------------- Jacob Skaria "Devabrata Mullick" wrote: By using this argument the outcomes is "#N/A" "Ashish Mathur" wrote: 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" <Devabrata wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup an exact Value with a range
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" wrote: 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 --------------- Jacob Skaria "Devabrata Mullick" wrote: By using this argument the outcomes is "#N/A" "Ashish Mathur" wrote: 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" <Devabrata wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup an exact Value with a range
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 --------------- Jacob Skaria "Devabrata Mullick" wrote: 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" wrote: 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 --------------- Jacob Skaria "Devabrata Mullick" wrote: By using this argument the outcomes is "#N/A" "Ashish Mathur" wrote: 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" <Devabrata wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup an exact Value with a range
thnkx.... if possiable i can send you the speciment data......
"Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Devabrata Mullick" wrote: 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" wrote: 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 --------------- Jacob Skaria "Devabrata Mullick" wrote: By using this argument the outcomes is "#N/A" "Ashish Mathur" wrote: 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" <Devabrata wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup an exact Value with a range
mail ID please...
"Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Devabrata Mullick" wrote: 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" wrote: 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 --------------- Jacob Skaria "Devabrata Mullick" wrote: By using this argument the outcomes is "#N/A" "Ashish Mathur" wrote: 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" <Devabrata wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup an exact Value with a range
hi Jacob,
This is not working properly....... It picks up the nearst value or the last value of the range when shorting .... "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Devabrata Mullick" wrote: 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" wrote: 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 --------------- Jacob Skaria "Devabrata Mullick" wrote: By using this argument the outcomes is "#N/A" "Ashish Mathur" wrote: 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" <Devabrata wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup Exact functions? | New Users to Excel | |||
| Need to do an EXACT LOOKUP | Excel Worksheet Functions | |||
| formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
| LookUp - Exact only | Excel Worksheet Functions | |||
| LookUp - Exact only | Excel Worksheet Functions | |||
Linear Mode
