Microsoft Excel - Vlookup
Asked By jyupil
04-May-11 03:04 AM
Hii Guyssss
I have a data in excel sheet like below:
|
|
A
|
B
|
C
|
D
|
E
|
F
|
G
|
|
1
|
Customer Name
|
ABC
|
XYZ
|
|
2
|
Contact No
|
123
|
980
|
|
3
|
Email ID
|
abcdefg
|
ghijklm
|
|
4
|
Product |
Rate 1
|
Rate 2
|
Rate 3
|
Rate 1
|
Rate 2
|
Rate 3
|
|
5
|
Pro 1 |
1 |
2 |
3 |
10 |
20 |
30 |
|
6
|
Pro 2 |
4 |
5 |
6 |
40 |
50 |
60 |
|
7
|
Pro 3 |
7 |
8 |
9 |
70 |
80 |
90 |
|
8
|
|
|
|
|
|
|
|
now i am trying to make a table with the help of lookup which gives me a value of rates, contact no with the vlookup value of customer name. the format of table which i want to use that is given below, in this case product are same for every customer, there will b a changes in contact detail and rates only. kindly someone please try to help me out and give me a solution.............
|
|
A
|
B
|
C
|
D
|
| 1 |
Customer Name |
ABC
|
| 2 |
Contact No |
|
| 3 |
Email ID |
|
| 4 |
Product |
Rate 1 |
Rate 2 |
Rate 3 |
| 5 |
Pro 1 |
|
|
|
| 6 |
Pro 2 |
|
|
|
| 7 |
Pro 3 |
|
|
|
R VENKAT replied to jyupil
sheet1 is your data like this (A1 to G7)
Customer Name ABC XYZ
Contact No 123 980
Email ID abcdefg ghijklm
Product Rate 1 Rate 2 Rate 3 Rate 1 Rate 2 Rate 3
Pro 1 1 2 3 10 20 30
Pro 2 4 5 6 40 50 60
Pro 3 7 8 9 70 80 90
sheet 2 will be like this A1 to D7
Customer Name ABC
Contact No
Email ID
Product Rate 1 Rate 2 Rate 3
Pro 1 1 2 3
Pro 2 4 5 6
Pro 3 7 8 9
the formula in B5 in sheet 2 will be
=VLOOKUP($A5,Sheet1!$A$4:$G$100,COLUMN(B$1),0)
this is copied down and to the right.
wally eye replied to jyupil
Try this in B2 (Contact No):
=HLOOKUP(B$1,Sheet1!B$1:G$3,ROW(Sheet1!B2),FALSE)
autofill down to C2. For B5 (Pro 1, Rate 1):
=OFFSET(Sheet1!A5,0,MATCH($B$1,Sheet1!$B$1:$F$1,0))
auto fill to B7, then autofill B5:B7 to D5:D7
It looks like you will be expanding your data set to the right, with more customer names. You will need to adjust the B$1:G$3 from the first formula and $B$1:$F$1 from the second to match your customer data range.
Again Vlookup CUSTOMER NAME XYZ ABC Contact No 04-2259651 04-2261350 FAX No 04-2255610 04-2269560 Email Add XYZ@yahoo.com ABC@kanz.com Category Price 1 Price 2 Price 3 Price 1 Price 2 Price 3 excel sheet1 from A1:G9 Now my sheet2 data is like below from A1:D8 CUSTOMER NAME Contact No FAX No Email ID Category Price 1 Price 2 Price 3 Product 1 me detail of Contact No, Fax No, Email & all Prices with the help of Cutomer Name (lookup Value). kindly give me a suggetion about the formula which i can utilize to shtml here you will get samples for Vlookup () Hi Jyupil I hope you have the customer name in Sheet2 cell A1. . .If it is in cell A2 change that in the
filter criteria in one q DataBase Hi, I have a data source table with a customer name field and various metrics fields, I'd like to sum metric "a" based on customer name being like "abc" and another sum of metric "a" where the customer name is NOT LIKE abc" in one query, but I can't seem to figure this out, but, I'm
field DataBase Hi there, I have a number of fields in my query. They are Customer, Document, Title. In this query, i have include a input dialog for the user to enter the customer name. In the Customer field, there are several customers in one record that uses the same document. Example Customer | Document | Title ABC / 3H / SST | 12346 | This is a test procedure ABC / 3H | 11234 | Procedure I would like the output shows a list of the documents used for the specified customer. Example, I want to know the list of documents that is for Customer: ABC so
Splitting a Field Question DataBase I have a query where I am pulling the customer name where it is formatted as Last, First in most cases. However, there are customers listed function and I need the two new fields not to show any comma. Example: Current Name Last Name First Name Smith, John Smith John ABC Company ABC Company If I use the following on Last I get an error on the ABC Company line. Last: Left([Name], InStr([Name], ", ")-1) Is there a way that I can