Vlookup Formula Help

G

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
 
P

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
 
G

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?
 
P

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
 
G

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
 
R

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:D$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.
 
M

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
 
R

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>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top