Formula

[Vickie Benton]

I want a formula that will look for a specific word in a cell on another sheet and give me the number that is in the next column of that row
Sheet 1 A4 is "ABC Company
Sheet 2 in Column A will be (maybe) the words "ABC Company" and in that same row in column B is the number 20
So I want in Sheet 1 in B4 to pull that number 20
I tried Sumif but I might be using it wrong
Thanks
Vickie

 

[Frank Kabel]

Hi vickie
try
=VLOOKUP(A4,'sheet2'!$A$1:$B$999,2,0)
this looks for an exact match of A4 in column A of your sheet2

Frank

 

[CLR]

This will get rid of the #N/A if no match is found, and leave the cell
blank..........

=IF(ISNA(VLOOKUP(A4,sheet2!$A$1:$B$999,2,0)),"",VLOOKUP(A4,sheet2!$A$1:$B$99
9,2,0))


Vaya con Dios,
Chuck, CABGx3

I want a formula that will look for a specific word in a cell on another

sheet and give me the number that is in the next column of that row.

Sheet 1 A4 is "ABC Company"
Sheet 2 in Column A will be (maybe) the words "ABC Company" and in that

same row in column B is the number 20.

 

[Bonnie S]

Yes, that is a good formula to use. If you have multiples
of ABC Company in one column and you want to add all the
totals in the same row as ABC. Then yes it will add all of
them up and bring you the total.

example:
=SUMIF('2004'!$C:$C,A22,'2004'!F:F)

2004 is by TAB
$C:$C is where I have names I want to look up and bring
totals over to another sheet
A22 is where I want it to see the name I want it to bring
over totals for
f:f is where the totals are

I hope that made sense.

I would be happy to give you more info

-----Original Message-----
I want a formula that will look for a specific word in a

cell on another sheet and give me the number that is in
the next column of that row.

Sheet 1 A4 is "ABC Company"
Sheet 2 in Column A will be (maybe) the words "ABC

Company" and in that same row in column B is the number 20.

 

[Vickie Benton]

All three responses have been helpful. Thank you
Now the problem is, the phrases have some spaces at the end, some have one or two. Is there a way to either tell it to disregard the spaces after the phrase or a way to eliminate the spaces at the end of the phrase
I sent this question earlier but it didn't seem to get posted. Sorry if it ends up being posted twice
Vickie

 

[Frank Kabel]

All three responses have been helpful. Thank you!
Now the problem is, the phrases have some spaces at the end, some
have one or two. Is there a way to either tell it to disregard the
spaces after the phrase or a way to eliminate the spaces at the end
of the phrase? I sent this question earlier but it didn't seem to get
posted. Sorry if it ends up being posted twice.
Vickie

Hi Vickie
try using TRIM on your lookup criteria
=VLOOKUP(TRIM(..),----)

Frank

 

[Vickie]

I am using this formula
=IF(ISNA(VLOOKUP(A4,Sheet2!$A$1:$B$999,2,0)),"0",VLOOKUP(A4,Sheet2!$A$1:$B$999,2,0)
Where do I put TRIM
Thank you
Vickie

 

[Frank Kabel]

I am using this formula.
=IF(ISNA(VLOOKUP(A4,Sheet2!$A$1:$B$999,2,0)),"0",VLOOKUP(A4,Sheet2!$A$1
:$B$999,2,0))
Where do I put TRIM?
Thank you,
Vickie

Hi Vickie
try
=IF(ISNA(VLOOKUP(TRIM(A4),Sheet2!$A$1:$B$999,2,0)),"0",VLOOKUP(TRIM(A4)
,Sheet2!$A$1:$B$999,2,0))

Frank