Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return value based on certain criteria
Hi all,
I have two separate worksheets within the same workbook The first worksheet (ConsultCall!) looks like this: ID# Percentage 1-1232 50% 2-345 61% 10-12 89% 15-98753 25% In another sheet (Calc!), I have a similar ID# column, but the ID #'s are not in the same order as the first worksheet and some ID #'s are in one that are not in the other column. Basically I want to be able to match on unique ID #'s and then return the corresponding percentage value in another column in the second worksheet for that ID # in the first worksheet. ID# 15-98753 10-12 2-345 Any thoughts? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return value based on certain criteria
Assuming:
A) the worksheet name does not actually include the ! B) the data in ConsultCall is in A1:B30 C) the date in Calc begins in A1 In B1 of Calc enter =VLOOKUP(A1,ConsultCall!$A$1:$B$30,2,FALSE) But this will give an #N/A error when the ConsultCall does not have corresponding ID Use IF(ISNA(VLOOKUP(A1,ConsultCall!$A$1:$B$30,2,FALSE) ),"X",VLOOKUP(A1,ConsultCall!$A$1:$B$30,2,FALSE) ) where X can be replaced by an message (or a blank) In Excel 2007 IFERROR(VLOOKUP(A1,ConsultCall!$A$1:$B$30,2,FALSE) ),"X") BEST WISHES -- Bernard Liengme http://people.stfx.ca/bliengme Microsoft Excel MVP "C." wrote in message ... Hi all, I have two separate worksheets within the same workbook The first worksheet (ConsultCall!) looks like this: ID# Percentage 1-1232 50% 2-345 61% 10-12 89% 15-98753 25% In another sheet (Calc!), I have a similar ID# column, but the ID #'s are not in the same order as the first worksheet and some ID #'s are in one that are not in the other column. Basically I want to be able to match on unique ID #'s and then return the corresponding percentage value in another column in the second worksheet for that ID # in the first worksheet. ID# 15-98753 10-12 2-345 Any thoughts? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return value based on certain criteria
Hi,
use =SUMPRODUCT(--(A1=ConsultCall!A1:A23),ConsultCall!B1:B23) change your range to fit your needs "C." wrote: Hi all, I have two separate worksheets within the same workbook The first worksheet (ConsultCall!) looks like this: ID# Percentage 1-1232 50% 2-345 61% 10-12 89% 15-98753 25% In another sheet (Calc!), I have a similar ID# column, but the ID #'s are not in the same order as the first worksheet and some ID #'s are in one that are not in the other column. Basically I want to be able to match on unique ID #'s and then return the corresponding percentage value in another column in the second worksheet for that ID # in the first worksheet. ID# 15-98753 10-12 2-345 Any thoughts? Thanks! . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return value based on certain criteria
On Dec 18, 10:16*am, Eduardo
wrote: Hi, use =SUMPRODUCT(--(A1=ConsultCall!A1:A23),ConsultCall!B1:B23) change your range to fit your needs "C." wrote: Hi all, I have two separate worksheets within the same workbook The first worksheet (ConsultCall!) looks like this: ID# * * * * * * * * Percentage 1-1232 * * * * * *50% 2-345 * * * * * * *61% 10-12 * * * * * * 89% 15-98753 * * * 25% In another sheet (Calc!), I have a similar ID# column, but the ID #'s are not in the same order as the first worksheet and some ID #'s are in one that are not in the other column. Basically I want to be able to match on unique ID #'s and then return the corresponding percentage value in another column in the second worksheet for that ID # in the first worksheet. ID# 15-98753 10-12 2-345 Any thoughts? Thanks! . Thank you both! Works great! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return value based on criteria | Excel Discussion (Misc queries) | |||
Return info based on 1 criteria | Excel Discussion (Misc queries) | |||
Return a value based on two criteria | Excel Discussion (Misc queries) | |||
Return number OR sum based on two criteria | Excel Worksheet Functions | |||
return value based on two criteria | Excel Worksheet Functions |