software

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

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 quot;Branchquot; and
quot;Addressquot; 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.

quot;PrincessMquot; wrote:

gt; i have a master data like this.
gt; A B C D
gt; E
gt; Customer Bank Bank A/C No. Branch Address
gt;
gt; and on the second sheet, i would like to just enter the Bank a/c on the first
gt; col, and the details will come out.
gt;
gt; Please help me on how to do that. I understand that i need to have the same
gt; coloumn name and stuffs like that, right?
gt;
gt; Thanks in advance.
gt;
gt; Regards,
gt; Merlina
gt;

Vlookup would require the quot;keyquot; 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.

quot;PrincessMquot; lt;u22004@uwegt; wrote in message news:60722cbe825c2@uwe...
gt;i have a master data like this.
gt; A B C D
gt; E
gt; Customer Bank Bank A/C No. Branch Address
gt;
gt; and on the second sheet, i would like to just enter the Bank a/c on the
gt; first
gt; col, and the details will come out.
gt;
gt; Please help me on how to do that. I understand that i need to have the
gt; same
gt; coloumn name and stuffs like that, right?
gt;
gt; Thanks in advance.
gt;
gt; Regards,
gt; Merlina
Assuming layout was changed (as per suggesting in previous posting)

Bank A/C No.BankCustomer 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)),quot;quot;,VLOOKUP($A2,Sheet1!$A$2:$E$20,COLUMN() ,FALSE))

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

HTH

quot;PrincessMquot; wrote:

gt; i have a master data like this.
gt; A B C D
gt; E
gt; Customer Bank Bank A/C No. Branch Address
gt;
gt; and on the second sheet, i would like to just enter the Bank a/c on the first
gt; col, and the details will come out.
gt;
gt; Please help me on how to do that. I understand that i need to have the same
gt; coloumn name and stuffs like that, right?
gt;
gt; Thanks in advance.
gt;
gt; Regards,
gt; Merlina
gt;

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