Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar to VLOOKUP?
Hi,
There are two excel sheets which contain the following two main columns - --------------------------------------------------- Order No (Column E3) | Item No (Column F3) --------------------------------------------------- I have to compare records across both sheets and find out those records that match on both conditions. If I have to match single column, then I would have used VLOOKUP function. But it looks like VLOOKUP doesn't work if I try to match two columns - i.e., If I write like - =VLOOKUP(E3,'Sheet2'!A4:B430,1,FALSE) - this works But if I write - =VLOOKUP(E3:F3,'Sheet2'!A4:B430,1,FALSE) - this doesn't seem to work So my question is - is there any functionality in Excel that I can use for matching 2 or more columns? Or is there any other alternative? Can some one let me know please. Thanks in advance, Harish Mohanbabu -- MBS Axapta - MVP http://www.harishm.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar to VLOOKUP?
couldn't you concatenate the 2 values in a helper column and then search for
that? -- Gary "Harish Mohanbabu" wrote in message ... Hi, There are two excel sheets which contain the following two main columns - --------------------------------------------------- Order No (Column E3) | Item No (Column F3) --------------------------------------------------- I have to compare records across both sheets and find out those records that match on both conditions. If I have to match single column, then I would have used VLOOKUP function. But it looks like VLOOKUP doesn't work if I try to match two columns - i.e., If I write like - =VLOOKUP(E3,'Sheet2'!A4:B430,1,FALSE) - this works But if I write - =VLOOKUP(E3:F3,'Sheet2'!A4:B430,1,FALSE) - this doesn't seem to work So my question is - is there any functionality in Excel that I can use for matching 2 or more columns? Or is there any other alternative? Can some one let me know please. Thanks in advance, Harish Mohanbabu -- MBS Axapta - MVP http://www.harishm.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar to VLOOKUP?
Hi Gary,
The thought did strike my mind. Because of the nature of data (the item names contains all kinds of strings!) the concatenation might become messy. But if there is no alternative then I really haven't got any other choice. Thanks for your reply anyway, Regards, Harish Mohanbabu -- MBS Axapta - MVP http://www.harishm.com/ "Gary Keramidas" wrote: couldn't you concatenate the 2 values in a helper column and then search for that? -- Gary |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar to VLOOKUP?
Hi
From VLOOKUP help, Lookup_value The value to search in the first column of the table array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.). Lookup_value can be a value or a reference. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value. The first parameter it wants is a value not a value list. Also what is your end goal? =VLOOKUP(E3:F3,'Sheet2'!A4:B430,1,FALSE) e.g. E3:F3 = 5 6 So you want to search for "5 6" two cells pattern in A4:B430 i.e. if A7:A8 = 5 6, so it will match Or do you want to just match 5 or 6 in the A4:B430? I think you may to write a macro to do the job. Here is KB for your reference. 141762 XL: How to Use Looping Structures in Visual Basic for Applications http://support.microsoft.com/?id=141762 Best regards, Peter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Vlookup help ... or something similar | Excel Worksheet Functions | |||
vlookup or similar help | Excel Worksheet Functions | |||
Similar to Vlookup | Excel Discussion (Misc queries) | |||
Procedure similar to VLookup - need help! | Excel Programming | |||
Help w/ Vlookup or similar | Excel Programming |