Match Command
 

Dear Friends,

I need code for to find the record through macth command instead of
vlookup command. It is because the data is not in a sort order, and i
don't want to change the sorting order of the original data.

When the data found then select the 3rd col of the row in a textbox1.

Thanks in advance

Syed Haider Ali


--
Syed Haider Ali
------------------------------------------------------------------------
Syed Haider Ali's Profile: http://www.excelforum.com/member.php...o&userid=21994
View this thread: http://www.excelforum.com/showthread...hreadid=397469

Match Command
 

You can use =vlookup() with data that isn't sorted. Just make sure you specify
False as the 4th argument.

=vlookup(a1,sheet2!a:d,3,false)

In code...

dim res as variant
dim myLookupRange as range
dim myCell as range

set mycell = worksheets("sheet1").range("a1")
set mylookuprange = worksheets("sheet2").range("A:D")

res = application.vlookup(mycell.value, mylookuprange,3,false)

if iserror(res) then
'not found
else
msgbox res
end if

====
I'm not sure what the 3rd column of the row in a textbox1 means.


Syed Haider Ali wrote:

Dear Friends,

I need code for to find the record through macth command instead of
vlookup command. It is because the data is not in a sort order, and i
don't want to change the sorting order of the original data.

When the data found then select the 3rd col of the row in a textbox1.

Thanks in advance

Syed Haider Ali

--
Syed Haider Ali
------------------------------------------------------------------------
Syed Haider Ali's Profile: http://www.excelforum.com/member.php...o&userid=21994
View this thread: http://www.excelforum.com/showthread...hreadid=397469

--

Dave Peterson

Match Command
 

U can use match function to index the column within vlookup as follows

suppose your data range is b2:g10
b2:g2 is a header for your table, col b is first colum in the table.
let's say that your header has a column labeled col3 and that xyz is a
record listed in the first column of your table.

vlookup () requires that first column in data range has record u r
looking up, this is the row anchor. u can combine the match function to
identify the column within the vlookup function as below:

=VLOOKUP("xyz", B2:G10, MATCH("col3",B2:G2,0),FALSE)