If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Vlookup Formula Help
I have to spread sheets with similar fields but one has ITEM number and the
other one doesn't. I need them to match so i can have the same item number on the same product form the other spread sheet. here is an example: I Have on both spread sheet: A B C D E Title Brand Gender name Size I need all the fields to match exactly so when Title,brand, gender ,name , size match it should return the item number that is on the second spread sheet: A B C D E F Title Brand Gender name Size Item# Any help ? thanks |
#2
|
|||
|
|||
Vlookup Formula Help
Insert a new column F on the second sheet and put this formula in
the =A2 & B2 & C2 & D2 & E2 Then copy this down as far as is necessary. Then in F2 of your first sheet you can put this formula: =VLOOKUP(A2&B2&C2&D2&E2,Sheet2!F:G,2,0) and then copy this down. If you have some entries for which there is no exact match in Sheet2, you will get the #N/A error - you might have extra space(s) in one or the other sheets, so check for these and correct. You might like to fix the values from this formula, and then you can delete the new column F from the second sheet. Hope this helps. Pete On Sep 18, 11:55*pm, Gmata wrote: I have to spread sheets with similar fields but one has ITEM number and the other one doesn't. I need them to match so i can have the same item number on the same product form the other spread sheet. here is an example: I Have on both spread sheet: * *A * * * B * * * * *C * * * *D * * * E * * * * * * * * * * * * * * * Title Brand *Gender name Size * * * * * * * I need all the fields to match exactly so when Title,brand, gender ,name , size match it should return the item number that is on the second spread sheet: * * A * * * B * * * * *C * * * *D * * * E * * *F * * * * * * * * * * * * Title Brand *Gender name Size Item# * * * * * * * Any help ? thanks |
#3
|
|||
|
|||
Vlookup Formula Help
Is there any formula that can search every single field and match exactly all
5 fields and return a sixth one which would be the Item number? even if i have to paste the other spread sheet next to each oter? |
#4
|
|||
|
|||
Vlookup Formula Help
Yes, I gave you one:
=VLOOKUP(A2&B2&C2&D2&E2,Sheet2!F:G,2,0) together with a method of temporarily setting up a new column F in Sheet2. This is the simplest approach. Pete On Sep 19, 12:34*am, Gmata wrote: Is there any formula that can search every single field and match exactly all 5 fields and return a sixth one which would be the Item number? even if i have to paste the other spread sheet next to each oter? |
#5
|
|||
|
|||
Vlookup Formula Help
Thanks Pete I am just a little bit confused by your formula sorry.
I tried to make it work but i get no results when you say on Sheet 2 on column F use this formula =A2 & B2 & C2 & D2 & E2 ( First problem is that i have information in column F thats were the Item Number is located, so i inserted the formula in column G) now do i include F2 on that formula too? (item Number) the result i get in column G is all the words and number together including the item number, Now on Sheet1 when i insert the formula =VLOOKUP(A2&B2&C2&D2&E2,Sheet2!F:G,2,0) i also need to insert it on K wich is the next available column, so would that change any information on this formula? i don't understand how is finding just the item number if its included with everything else and where it says sheet2!F:G should it be G:H ?? since i added the formula on G ? Thanks |
#6
|
|||
|
|||
Vlookup Formula Help
Try this *array* formula in F2 of the first sheet:
=INDEX(Sheet2!F$2:F$20,MATCH(1,(Sheet2!A$2:A$20=A2 )*(Sheet2!B$2:B$20=B2)*(Sheet2!C$2:C$20=C2)*(Sheet 2!D$2$20=D2)*(Sheet2!E$2:E$20=E2),0)) -- Array formulas must be entered with CSE, Ctrl Shift Enter, instead of the regular Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. *After* the CSE entry copy down as needed. Adjust your ranges as necessary. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Gmata" wrote in message ... Is there any formula that can search every single field and match exactly all 5 fields and return a sixth one which would be the Item number? even if i have to paste the other spread sheet next to each oter? |
#7
|
|||
|
|||
Vlookup Formula Help
Thanks HTH it worked perfectly!
and thanks to you to Pete! |
#8
|
|||
|
|||
Vlookup Formula Help
Is there anyway i can use Vlookup or any other lookup function, looking for a
specific value in Column A and the function returns sum of all the values target Column? E.g. Column A has values 1 1 2 1 3 and column B has relative values 23 24 56 34 98 I need to look for '1' in column A and add up respective values in Column B. Output for value 1 should be 81 Any help is appreciated Thanks |
#9
|
|||
|
|||
Vlookup Formula Help
Hi,
You can use the SUMIF() function =sumif(A1;A5,1,B1:B5) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "manas" wrote in message ... Is there anyway i can use Vlookup or any other lookup function, looking for a specific value in Column A and the function returns sum of all the values target Column? E.g. Column A has values 1 1 2 1 3 and column B has relative values 23 24 56 34 98 I need to look for '1' in column A and add up respective values in Column B. Output for value 1 should be 81 Any help is appreciated Thanks |
#10
|
|||
|
|||
Vlookup Formula Help
Thanks Ashish, it was really helpful, appreciate
"Ashish Mathur" wrote: Hi, You can use the SUMIF() function =sumif(A1;A5,1,B1:B5) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "manas" wrote in message ... Is there anyway i can use Vlookup or any other lookup function, looking for a specific value in Column A and the function returns sum of all the values target Column? E.g. Column A has values 1 1 2 1 3 and column B has relative values 23 24 56 34 98 I need to look for '1' in column A and add up respective values in Column B. Output for value 1 should be 81 Any help is appreciated Thanks |
|
Thread Tools | |
Display Modes | |
|
|