How to use VLOOKUP or HLOOKUP

[vbanovice123]

Hi,

As always this forum is the best and has been very quick to response with the most accurate solutions. Thanks you so much for all your answers to the posts submitted!

I have another question. I have two different workbooks with various columns. There are three to four column names that are similar. I have a column called Account Number.

The goal is to find where the mismatch is between the two workbooks for this column. I have a macro and ca use that. But If we want to use VLOOKUP is it possible and how?

I heard that VLOOKUP can lookup a column in another workbook and return TRUE or FALSE?

I did not understand what the table array and column number indicate in the VLOOKUP parameters.

Thanks!

 

[TiaXL]

like this

"=vlookup(Value you want to look for,data to look in,how many columns data is in,false)

so for example

=vlookup(a2,a:ab,1,false)
says look for the value in cell a2 in columns a:ab 1 column in. Hope that makes sense

you can look in other sheets/workbooks by including the name like

=VLOOKUP(A38,Sheet1!A:A,1,0) or =VLOOKUP(A38,[Book1]Sheet1!A:A,1,0)

HLOOKUP is the same i think (unsure as I never used it) but principals are horizontal instead of vertical.

 

[Comfy]

VLOOKUP(Lookup Value, table array, col_index_num, [range_lookup])

The lookup Value is the Value that you are trying to Find.

Table array is the table within which you are looking for the lookup value.

Col Index Num is the column from which the function will return a Value. The must be to the right of the vlookup Value.


In my example below we're looking for the value 123 in the able that starts in C3 and ends in D6.

We asked the function to return from col index 2, so the result is 984.

You can either use an if statement to get True or false, or the vlookup on its own, if it does not find a match it will return #N/A

Excel Workbook
	A	B	C	D
2			Column Index 1	Column Index 2
3	123	984	123	984
4		TRUE	654	984
5	987	#N/A	65	89498
6			65	1981
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B3	=VLOOKUP(A3,C3:D6,2,FALSE)
B4	=IF(VLOOKUP(A3,C3:D6,2,FALSE),TRUE,FALSE)
B5	=VLOOKUP(A5,C3:D6,2,FALSE)