Vlookup Formula Help

[Gmata]

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

 

[Pete_UK]

Insert a new column F on the second sheet and put this formula in
there:

=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

 

[Gmata]

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?

 

[Pete_UK]

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

 

[Gmata]

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

 

[RagDyer]

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)*(Sheet2!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.

 

[Gmata]

Thanks HTH it worked perfectly!
and thanks to you to Pete!

 

[manas]

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

 

[Ashish Mathur]

Hi,

You can use the SUMIF() function

=sumif(A1;A5,1,B1:B5)

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[manas]

Thanks Ashish, it was really helpful, appreciate

Hi,

You can use the SUMIF() function

=sumif(A1;A5,1,B1:B5)

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ashish Mathur]

Thank you Manas. All the best

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[RagDyeR]

You're welcome, and thanks for the feed-back ... even though my initials are
RD, and the HTH stands for "Hope This Helps"<g>