Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please Help me with VLOOKUP function
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please Help me with VLOOKUP function
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. "PrincessM" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please Help me with VLOOKUP function
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. "PrincessM" <u22004@uwe wrote in message news:60722cbe825c2@uwe... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please Help me with VLOOKUP function
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 "PrincessM" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please Help me with VLOOKUP function
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup worksheet function | Excel Worksheet Functions | |||
Vlookup with function in table_array | Excel Worksheet Functions | |||
format cell based on results of vlookup function | Excel Worksheet Functions | |||
Vlookup w/Date Function | Excel Worksheet Functions | |||
Regarding IF function or vLOOKUP function | Excel Worksheet Functions |