Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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


Vickie Benton said:
I want a formula that will look for a specific word in a cell on another
Click to expand...
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
Click to expand...
same row in column B is the number 20.
 
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
Click to expand...
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
Click to expand...
Company" and in that same row in column B is the number 20.
 
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
 
Vickie said:
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
Click to expand...

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

Frank
 
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
 
Vickie said:
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
Click to expand...

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
 
Back
Top