Similar to VLOOKUP?

[Harish Mohanbabu]

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/

[Gary Keramidas]

couldn't you concatenate the 2 values in a helper column and then search for
that?

--


Gary


"Harish Mohanbabu" <[email protected]> wrote in message
news:[email protected]...
> 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/

[Harish Mohanbabu]

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

[Peter Huang [MSFT]]

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.