| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to lookup cell value
On worksheet 1 in A2 I have a cell value of American Advantage. In cell A1 I need the value on worksheet to of the percent (0.43%). I need a formula to do something like this. If worksheet 2 A1:A6 = worksheet 1 A2, then let A1= worksheet b2. Then if the fund is not in list I need worksheet A1 to equal 0.00% This would be what worksheet 2 would look like. AIM BASIC BALANCED $10,630.02 0.29% AMERICAN ADVANTAGE SM CAP VAL $15,738.30 0.43% AMERICAN BALANCED FUND R3 $12,906.04 0.35% COLUMBIA ACORN $64,048.18 1.75% EUROPACIFIC GROWTH $213,617.45 5.84% Franklin Small-Mid Cap Growth $194,030.84 5.30% Any suggestions. I used vlookup which works except for when the fund is not in the list and I need a results of 0.00% |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to lookup cell value
I'm guessing you get #N/A if there is no match. Try using this
=if(isna(VLOOKUP()),0,vlookup()) "sweetsue516" wrote: On worksheet 1 in A2 I have a cell value of American Advantage. In cell A1 I need the value on worksheet to of the percent (0.43%). I need a formula to do something like this. If worksheet 2 A1:A6 = worksheet 1 A2, then let A1= worksheet b2. Then if the fund is not in list I need worksheet A1 to equal 0.00% This would be what worksheet 2 would look like. AIM BASIC BALANCED $10,630.02 0.29% AMERICAN ADVANTAGE SM CAP VAL $15,738.30 0.43% AMERICAN BALANCED FUND R3 $12,906.04 0.35% COLUMBIA ACORN $64,048.18 1.75% EUROPACIFIC GROWTH $213,617.45 5.84% Franklin Small-Mid Cap Growth $194,030.84 5.30% Any suggestions. I used vlookup which works except for when the fund is not in the list and I need a results of 0.00% |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to lookup cell value
Barb,
It doesn't like the 0 in the middle of the , ,. I don't know if I am doing something wrong or if I need a different formula. I don't understand this one. Can you explain "Barb Reinhardt" wrote: I'm guessing you get #N/A if there is no match. Try using this =if(isna(VLOOKUP()),0,vlookup()) "sweetsue516" wrote: On worksheet 1 in A2 I have a cell value of American Advantage. In cell A1 I need the value on worksheet to of the percent (0.43%). I need a formula to do something like this. If worksheet 2 A1:A6 = worksheet 1 A2, then let A1= worksheet b2. Then if the fund is not in list I need worksheet A1 to equal 0.00% This would be what worksheet 2 would look like. AIM BASIC BALANCED $10,630.02 0.29% AMERICAN ADVANTAGE SM CAP VAL $15,738.30 0.43% AMERICAN BALANCED FUND R3 $12,906.04 0.35% COLUMBIA ACORN $64,048.18 1.75% EUROPACIFIC GROWTH $213,617.45 5.84% Franklin Small-Mid Cap Growth $194,030.84 5.30% Any suggestions. I used vlookup which works except for when the fund is not in the list and I need a results of 0.00% |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to lookup cell value
Where i have VLOOKUP(), put your entire VLOOKUP formula.
"sweetsue516" wrote: Barb, It doesn't like the 0 in the middle of the , ,. I don't know if I am doing something wrong or if I need a different formula. I don't understand this one. Can you explain "Barb Reinhardt" wrote: I'm guessing you get #N/A if there is no match. Try using this =if(isna(VLOOKUP()),0,vlookup()) "sweetsue516" wrote: On worksheet 1 in A2 I have a cell value of American Advantage. In cell A1 I need the value on worksheet to of the percent (0.43%). I need a formula to do something like this. If worksheet 2 A1:A6 = worksheet 1 A2, then let A1= worksheet b2. Then if the fund is not in list I need worksheet A1 to equal 0.00% This would be what worksheet 2 would look like. AIM BASIC BALANCED $10,630.02 0.29% AMERICAN ADVANTAGE SM CAP VAL $15,738.30 0.43% AMERICAN BALANCED FUND R3 $12,906.04 0.35% COLUMBIA ACORN $64,048.18 1.75% EUROPACIFIC GROWTH $213,617.45 5.84% Franklin Small-Mid Cap Growth $194,030.84 5.30% Any suggestions. I used vlookup which works except for when the fund is not in the list and I need a results of 0.00% |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to lookup cell value
=if(isna(VLOOKUP(B7,'MH AC'!A9:C25,3),0,VLOOKUP(B7,'MH AC'!A9:C25,3)
Is this correct? The cell and worksheet references should be the same in both vlookups? "Barb Reinhardt" wrote: Where i have VLOOKUP(), put your entire VLOOKUP formula. "sweetsue516" wrote: Barb, It doesn't like the 0 in the middle of the , ,. I don't know if I am doing something wrong or if I need a different formula. I don't understand this one. Can you explain "Barb Reinhardt" wrote: I'm guessing you get #N/A if there is no match. Try using this =if(isna(VLOOKUP()),0,vlookup()) "sweetsue516" wrote: On worksheet 1 in A2 I have a cell value of American Advantage. In cell A1 I need the value on worksheet to of the percent (0.43%). I need a formula to do something like this. If worksheet 2 A1:A6 = worksheet 1 A2, then let A1= worksheet b2. Then if the fund is not in list I need worksheet A1 to equal 0.00% This would be what worksheet 2 would look like. AIM BASIC BALANCED $10,630.02 0.29% AMERICAN ADVANTAGE SM CAP VAL $15,738.30 0.43% AMERICAN BALANCED FUND R3 $12,906.04 0.35% COLUMBIA ACORN $64,048.18 1.75% EUROPACIFIC GROWTH $213,617.45 5.84% Franklin Small-Mid Cap Growth $194,030.84 5.30% Any suggestions. I used vlookup which works except for when the fund is not in the list and I need a results of 0.00% |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to lookup cell value
Is A9 to A25 sorted, ascending?
Since you're *not* using the 4th argument of Vlookup, the lookup table *must* be sorted ascending for the return to be accurate. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "sweetsue516" wrote in message ... =if(isna(VLOOKUP(B7,'MH AC'!A9:C25,3),0,VLOOKUP(B7,'MH AC'!A9:C25,3) Is this correct? The cell and worksheet references should be the same in both vlookups? "Barb Reinhardt" wrote: Where i have VLOOKUP(), put your entire VLOOKUP formula. "sweetsue516" wrote: Barb, It doesn't like the 0 in the middle of the , ,. I don't know if I am doing something wrong or if I need a different formula. I don't understand this one. Can you explain "Barb Reinhardt" wrote: I'm guessing you get #N/A if there is no match. Try using this =if(isna(VLOOKUP()),0,vlookup()) "sweetsue516" wrote: On worksheet 1 in A2 I have a cell value of American Advantage. In cell A1 I need the value on worksheet to of the percent (0.43%). I need a formula to do something like this. If worksheet 2 A1:A6 = worksheet 1 A2, then let A1= worksheet b2. Then if the fund is not in list I need worksheet A1 to equal 0.00% This would be what worksheet 2 would look like. AIM BASIC BALANCED $10,630.02 0.29% AMERICAN ADVANTAGE SM CAP VAL $15,738.30 0.43% AMERICAN BALANCED FUND R3 $12,906.04 0.35% COLUMBIA ACORN $64,048.18 1.75% EUROPACIFIC GROWTH $213,617.45 5.84% Franklin Small-Mid Cap Growth $194,030.84 5.30% Any suggestions. I used vlookup which works except for when the fund is not in the list and I need a results of 0.00% |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to lookup cell value
RagDyer,
I thought that was for lookup formula. Let me give the sort a try. "RagDyer" wrote: Is A9 to A25 sorted, ascending? Since you're *not* using the 4th argument of Vlookup, the lookup table *must* be sorted ascending for the return to be accurate. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "sweetsue516" wrote in message ... =if(isna(VLOOKUP(B7,'MH AC'!A9:C25,3),0,VLOOKUP(B7,'MH AC'!A9:C25,3) Is this correct? The cell and worksheet references should be the same in both vlookups? "Barb Reinhardt" wrote: Where i have VLOOKUP(), put your entire VLOOKUP formula. "sweetsue516" wrote: Barb, It doesn't like the 0 in the middle of the , ,. I don't know if I am doing something wrong or if I need a different formula. I don't understand this one. Can you explain "Barb Reinhardt" wrote: I'm guessing you get #N/A if there is no match. Try using this =if(isna(VLOOKUP()),0,vlookup()) "sweetsue516" wrote: On worksheet 1 in A2 I have a cell value of American Advantage. In cell A1 I need the value on worksheet to of the percent (0.43%). I need a formula to do something like this. If worksheet 2 A1:A6 = worksheet 1 A2, then let A1= worksheet b2. Then if the fund is not in list I need worksheet A1 to equal 0.00% This would be what worksheet 2 would look like. AIM BASIC BALANCED $10,630.02 0.29% AMERICAN ADVANTAGE SM CAP VAL $15,738.30 0.43% AMERICAN BALANCED FUND R3 $12,906.04 0.35% COLUMBIA ACORN $64,048.18 1.75% EUROPACIFIC GROWTH $213,617.45 5.84% Franklin Small-Mid Cap Growth $194,030.84 5.30% Any suggestions. I used vlookup which works except for when the fund is not in the list and I need a results of 0.00% |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to lookup cell value
I can get lookup and vlookup formulas to work great, but I run into problems
when the value I want looked up isn't listed. When I value is not listed, I need it to display 0.00 "sweetsue516" wrote: RagDyer, I thought that was for lookup formula. Let me give the sort a try. "RagDyer" wrote: Is A9 to A25 sorted, ascending? Since you're *not* using the 4th argument of Vlookup, the lookup table *must* be sorted ascending for the return to be accurate. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "sweetsue516" wrote in message ... =if(isna(VLOOKUP(B7,'MH AC'!A9:C25,3),0,VLOOKUP(B7,'MH AC'!A9:C25,3) Is this correct? The cell and worksheet references should be the same in both vlookups? "Barb Reinhardt" wrote: Where i have VLOOKUP(), put your entire VLOOKUP formula. "sweetsue516" wrote: Barb, It doesn't like the 0 in the middle of the , ,. I don't know if I am doing something wrong or if I need a different formula. I don't understand this one. Can you explain "Barb Reinhardt" wrote: I'm guessing you get #N/A if there is no match. Try using this =if(isna(VLOOKUP()),0,vlookup()) "sweetsue516" wrote: On worksheet 1 in A2 I have a cell value of American Advantage. In cell A1 I need the value on worksheet to of the percent (0.43%). I need a formula to do something like this. If worksheet 2 A1:A6 = worksheet 1 A2, then let A1= worksheet b2. Then if the fund is not in list I need worksheet A1 to equal 0.00% This would be what worksheet 2 would look like. AIM BASIC BALANCED $10,630.02 0.29% AMERICAN ADVANTAGE SM CAP VAL $15,738.30 0.43% AMERICAN BALANCED FUND R3 $12,906.04 0.35% COLUMBIA ACORN $64,048.18 1.75% EUROPACIFIC GROWTH $213,617.45 5.84% Franklin Small-Mid Cap Growth $194,030.84 5.30% Any suggestions. I used vlookup which works except for when the fund is not in the list and I need a results of 0.00% |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to lookup cell value
That's what the first equation I gave you will do.
"sweetsue516" wrote: I can get lookup and vlookup formulas to work great, but I run into problems when the value I want looked up isn't listed. When I value is not listed, I need it to display 0.00 "sweetsue516" wrote: RagDyer, I thought that was for lookup formula. Let me give the sort a try. "RagDyer" wrote: Is A9 to A25 sorted, ascending? Since you're *not* using the 4th argument of Vlookup, the lookup table *must* be sorted ascending for the return to be accurate. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "sweetsue516" wrote in message ... =if(isna(VLOOKUP(B7,'MH AC'!A9:C25,3),0,VLOOKUP(B7,'MH AC'!A9:C25,3) Is this correct? The cell and worksheet references should be the same in both vlookups? "Barb Reinhardt" wrote: Where i have VLOOKUP(), put your entire VLOOKUP formula. "sweetsue516" wrote: Barb, It doesn't like the 0 in the middle of the , ,. I don't know if I am doing something wrong or if I need a different formula. I don't understand this one. Can you explain "Barb Reinhardt" wrote: I'm guessing you get #N/A if there is no match. Try using this =if(isna(VLOOKUP()),0,vlookup()) "sweetsue516" wrote: On worksheet 1 in A2 I have a cell value of American Advantage. In cell A1 I need the value on worksheet to of the percent (0.43%). I need a formula to do something like this. If worksheet 2 A1:A6 = worksheet 1 A2, then let A1= worksheet b2. Then if the fund is not in list I need worksheet A1 to equal 0.00% This would be what worksheet 2 would look like. AIM BASIC BALANCED $10,630.02 0.29% AMERICAN ADVANTAGE SM CAP VAL $15,738.30 0.43% AMERICAN BALANCED FUND R3 $12,906.04 0.35% COLUMBIA ACORN $64,048.18 1.75% EUROPACIFIC GROWTH $213,617.45 5.84% Franklin Small-Mid Cap Growth $194,030.84 5.30% Any suggestions. I used vlookup which works except for when the fund is not in the list and I need a results of 0.00% |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to lookup cell value
Barb,
I posted a copy of that equation. Did you see it. For some reason it is not working. Does my list need to be sorted ascending? "Barb Reinhardt" wrote: That's what the first equation I gave you will do. "sweetsue516" wrote: I can get lookup and vlookup formulas to work great, but I run into problems when the value I want looked up isn't listed. When I value is not listed, I need it to display 0.00 "sweetsue516" wrote: RagDyer, I thought that was for lookup formula. Let me give the sort a try. "RagDyer" wrote: Is A9 to A25 sorted, ascending? Since you're *not* using the 4th argument of Vlookup, the lookup table *must* be sorted ascending for the return to be accurate. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "sweetsue516" wrote in message ... =if(isna(VLOOKUP(B7,'MH AC'!A9:C25,3),0,VLOOKUP(B7,'MH AC'!A9:C25,3) Is this correct? The cell and worksheet references should be the same in both vlookups? "Barb Reinhardt" wrote: Where i have VLOOKUP(), put your entire VLOOKUP formula. "sweetsue516" wrote: Barb, It doesn't like the 0 in the middle of the , ,. I don't know if I am doing something wrong or if I need a different formula. I don't understand this one. Can you explain "Barb Reinhardt" wrote: I'm guessing you get #N/A if there is no match. Try using this =if(isna(VLOOKUP()),0,vlookup()) "sweetsue516" wrote: On worksheet 1 in A2 I have a cell value of American Advantage. In cell A1 I need the value on worksheet to of the percent (0.43%). I need a formula to do something like this. If worksheet 2 A1:A6 = worksheet 1 A2, then let A1= worksheet b2. Then if the fund is not in list I need worksheet A1 to equal 0.00% This would be what worksheet 2 would look like. AIM BASIC BALANCED $10,630.02 0.29% AMERICAN ADVANTAGE SM CAP VAL $15,738.30 0.43% AMERICAN BALANCED FUND R3 $12,906.04 0.35% COLUMBIA ACORN $64,048.18 1.75% EUROPACIFIC GROWTH $213,617.45 5.84% Franklin Small-Mid Cap Growth $194,030.84 5.30% Any suggestions. I used vlookup which works except for when the fund is not in the list and I need a results of 0.00% |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to lookup cell value
Barb,
I went back to double check the equation you gave me. I found the problem...I missed a ( It works perfectly!!! "sweetsue516" wrote: On worksheet 1 in A2 I have a cell value of American Advantage. In cell A1 I need the value on worksheet to of the percent (0.43%). I need a formula to do something like this. If worksheet 2 A1:A6 = worksheet 1 A2, then let A1= worksheet b2. Then if the fund is not in list I need worksheet A1 to equal 0.00% This would be what worksheet 2 would look like. AIM BASIC BALANCED $10,630.02 0.29% AMERICAN ADVANTAGE SM CAP VAL $15,738.30 0.43% AMERICAN BALANCED FUND R3 $12,906.04 0.35% COLUMBIA ACORN $64,048.18 1.75% EUROPACIFIC GROWTH $213,617.45 5.84% Franklin Small-Mid Cap Growth $194,030.84 5.30% Any suggestions. I used vlookup which works except for when the fund is not in the list and I need a results of 0.00% |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to lookup cell value
Try sorting column A in ascending order.
"sweetsue516" wrote: Barb, I posted a copy of that equation. Did you see it. For some reason it is not working. Does my list need to be sorted ascending? "Barb Reinhardt" wrote: That's what the first equation I gave you will do. "sweetsue516" wrote: I can get lookup and vlookup formulas to work great, but I run into problems when the value I want looked up isn't listed. When I value is not listed, I need it to display 0.00 "sweetsue516" wrote: RagDyer, I thought that was for lookup formula. Let me give the sort a try. "RagDyer" wrote: Is A9 to A25 sorted, ascending? Since you're *not* using the 4th argument of Vlookup, the lookup table *must* be sorted ascending for the return to be accurate. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "sweetsue516" wrote in message ... =if(isna(VLOOKUP(B7,'MH AC'!A9:C25,3),0,VLOOKUP(B7,'MH AC'!A9:C25,3) Is this correct? The cell and worksheet references should be the same in both vlookups? "Barb Reinhardt" wrote: Where i have VLOOKUP(), put your entire VLOOKUP formula. "sweetsue516" wrote: Barb, It doesn't like the 0 in the middle of the , ,. I don't know if I am doing something wrong or if I need a different formula. I don't understand this one. Can you explain "Barb Reinhardt" wrote: I'm guessing you get #N/A if there is no match. Try using this =if(isna(VLOOKUP()),0,vlookup()) "sweetsue516" wrote: On worksheet 1 in A2 I have a cell value of American Advantage. In cell A1 I need the value on worksheet to of the percent (0.43%). I need a formula to do something like this. If worksheet 2 A1:A6 = worksheet 1 A2, then let A1= worksheet b2. Then if the fund is not in list I need worksheet A1 to equal 0.00% This would be what worksheet 2 would look like. AIM BASIC BALANCED $10,630.02 0.29% AMERICAN ADVANTAGE SM CAP VAL $15,738.30 0.43% AMERICAN BALANCED FUND R3 $12,906.04 0.35% COLUMBIA ACORN $64,048.18 1.75% EUROPACIFIC GROWTH $213,617.45 5.84% Franklin Small-Mid Cap Growth $194,030.84 5.30% Any suggestions. I used vlookup which works except for when the fund is not in the list and I need a results of 0.00% |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to lookup cell value
I forgot to say "Thank you"
Thank you! "Barb Reinhardt" wrote: Try sorting column A in ascending order. "sweetsue516" wrote: Barb, I posted a copy of that equation. Did you see it. For some reason it is not working. Does my list need to be sorted ascending? "Barb Reinhardt" wrote: That's what the first equation I gave you will do. "sweetsue516" wrote: I can get lookup and vlookup formulas to work great, but I run into problems when the value I want looked up isn't listed. When I value is not listed, I need it to display 0.00 "sweetsue516" wrote: RagDyer, I thought that was for lookup formula. Let me give the sort a try. "RagDyer" wrote: Is A9 to A25 sorted, ascending? Since you're *not* using the 4th argument of Vlookup, the lookup table *must* be sorted ascending for the return to be accurate. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "sweetsue516" wrote in message ... =if(isna(VLOOKUP(B7,'MH AC'!A9:C25,3),0,VLOOKUP(B7,'MH AC'!A9:C25,3) Is this correct? The cell and worksheet references should be the same in both vlookups? "Barb Reinhardt" wrote: Where i have VLOOKUP(), put your entire VLOOKUP formula. "sweetsue516" wrote: Barb, It doesn't like the 0 in the middle of the , ,. I don't know if I am doing something wrong or if I need a different formula. I don't understand this one. Can you explain "Barb Reinhardt" wrote: I'm guessing you get #N/A if there is no match. Try using this =if(isna(VLOOKUP()),0,vlookup()) "sweetsue516" wrote: On worksheet 1 in A2 I have a cell value of American Advantage. In cell A1 I need the value on worksheet to of the percent (0.43%). I need a formula to do something like this. If worksheet 2 A1:A6 = worksheet 1 A2, then let A1= worksheet b2. Then if the fund is not in list I need worksheet A1 to equal 0.00% This would be what worksheet 2 would look like. AIM BASIC BALANCED $10,630.02 0.29% AMERICAN ADVANTAGE SM CAP VAL $15,738.30 0.43% AMERICAN BALANCED FUND R3 $12,906.04 0.35% COLUMBIA ACORN $64,048.18 1.75% EUROPACIFIC GROWTH $213,617.45 5.84% Franklin Small-Mid Cap Growth $194,030.84 5.30% Any suggestions. I used vlookup which works except for when the fund is not in the list and I need a results of 0.00% |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
