Please Help me with VLOOKUP function

[PrincessM]

i have a master data like this.
A B C D
E
Customer Bank Bank A/C No. Branch Address

and on the second sheet, i would like to just enter the Bank a/c on the first
col, and the details will come out.

Please help me on how to do that. I understand that i need to have the same
coloumn name and stuffs like that, right?

Thanks in advance.

Regards,
Merlina

 

[Miguel Zapico]

In this case the limitation may be in the layout, not on the column names.
The VLOOKUP formula looks at a column and return other columns on the right
of it, so in your case you will only be able to retrieve "Branch" and
"Address" with the formula.
With the example, if you have 100 rows of data, and the A/C to look is on
cell F1, you can use VLOOKUP this way:
Branch: =VLOOKUP(F1,$C$2:$E$100,2,FALSE)
Address: =VLOOKUP(F1,$C$2:$E$100,3,FALSE)
If you can move the A/C column to the column A you can retrieve all the
information, changing the range to $A100:$E100 third parameter to the column
desired (2 for column B, 3 for column C and so on)

Hope this helps,
Miguel.

 

[Larry S]

Vlookup would require the "key" or lookup value to be on the left side of
the table/array (left most column), in your case, the Account #. Look to use
the LOOKUP function.

 

[Toppers]

Assuming layout was changed (as per suggesting in previous posting)

Bank A/C No. Bank Customer Branch Address

Then:

in B2 of second sheet put:

=VLOOKUP($A2,Sheet1!$A$2:$E$20,COLUMN(),FALSE)

Copy across to column E.

Change range $A$2:$E$20 to suit your requirements.

To allow for errors and to copy down as required use:

=IF(ISERROR(VLOOKUP($A2,Sheet1!$A$2:$E$20,COLUMN(),FALSE)),"",VLOOKUP($A2,Sheet1!$A$2:$E$20,COLUMN(),FALSE))

This will leave cells blank until A/C no. is entered

HTH

 

[Pete_UK]

INDEX and MATCH will perform the same function as a VLOOKUP, with the
advantage that the search column does not have to be on the extreme
left of the table.

Hope this helps.

Pete