Formula to look for unique ID in database & return value in cell to the right

[s_richard]

I need a formula that can look across a number of columns & rows for a unique ID, & once it finds that ID I need the formula to return whatever value is in the cell directly to the right of the unique ID...I'm sure excel has the capability to do this however I am currently having no luck after trying all day to get something to work. Any help would be much appreciated...

 

[s_richard]

No, one of my WkSKU values was in column C, the other was in column E. Didn't the formula correctly return values from columns D and F respectively for those two WkSKU values?

Notice that the first range in the SUMIF starts at the first WkSKU column and ends at the last WkSKU column. The second range starts at the first sales column and ends at the last sales column.

Is it as simple as this? Will this definitely work?

I've been scratching my brain at work all day over this, am home now (it's almost midnight) & am having trouble logging into the server to get into any files to check it. Will try first thing in the morning & re-post to let you know how I went.

If this works & it is as simple as this, then thank-you very much in advance...such a simple formula, neither me or my boss have been able to come to a solution for this

 

[Peter_SSs]

Is it as simple as this? Will this definitely work?

Provided the following holds, I believe it will.

.. identifiers are unique and the value to be returned is numerical, ..

Will try first thing in the morning & re-post to let you know how I went.

I'll look forward to your report.

 

[Peter_SSs]

am home now (it's almost midnight)

You must be in the same time zone as me.

 

[markmzz]

Another formula (array formula - use Ctrl+Shift+Enter and not only Enter):


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">WkSKU</td><td style="text-align: center;;">S</td><td style="text-align: center;;">WkSKU</td><td style="text-align: center;;">S</td><td style="text-align: center;;">WkSKU</td><td style="text-align: center;;">S</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">1-4567</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2-4567</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3-4567</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">6-3425</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7-3425</td><td style="text-align: center;;">7</td><td style="text-align: center;;">8-3425</td><td style="text-align: center;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">WkSKU</td><td style="text-align: center;;">Sales</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">8-4567</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">7-3425</td><td style="text-align: center;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">2-4567</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">4-8967</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">0-3271</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">1-4567</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">6-3425</td><td style="text-align: center;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">A2=WkSKU,Sales</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Sales</th><td style="text-align:left">=Sheet1!$B$2:$F$3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">WkSKU</th><td style="text-align:left">=Sheet1!$A$2:$E$3</td></tr></tbody></table></td></tr></table><br />
Note: the Peter_SSs's formula is ok.


Markmzz

 

[s_richard]

Provided the following holds, I believe it will.


I'll look forward to your report.

Awesome. works like a charm. Thanks very much for your help

 

[Peter_SSs]

works like a charm. Thanks very much for your help

Somehow I thought it would.
But thanks for the feedback.

 

[Erik_Pgh]

Thanks, before I try what do the the values in inverted columns represent?

Also the unique ID is always moving column & row number...is this formula appropriate? It looks like under the match formula you have to pre-select a column to look for a value to return? This needs to allow for the unique ID & value to move...or is this part of the formula for the lookup reference?

=IF(Selection_Col=" ","*",INDEX(Database,MATCH(Selection_Col,ColumnSelect,0),Selection_Col_Num))


Database - is the full listing of data including headers
Selection_Column - is your selection to the reference where the match is made within the database
ColumnSelect - is the column within the database that is matched to the selection_column
Selection_Col_Number - is the column number within the database that you want the returned value.

General notes:

With the index and Match it doesn't matter if its alpha or numeric
blanks return an error
Database doesn't need to be in order
Generally it is best to use named ranges but not required

https://sites.google.com/site/turns...iles/IndexMatchFormula.xls?attredirects=0&d=1

 

[Peter_SSs]

Erik, I'm not sure how this relates to the problem posed in this thread?
There are two (related) reasons:

1. Your formula is looking to match a value in 'ColumnSelect' which appears to be a named range referring to a particular column in the database. In this thread, it is not known which column the 'value of interest' (a WkSKU number) resides in.

2. Selection_Col_Number - is the column number within the database that you want the returned value
Since the column of the WkSKU number is unknown, so is the next column to the right, which is where the result is to be drawn from. So setting up the defined name 'Selection_Col_Number' would be difficult since it could change each time the WkSKU number of interest changes.