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

 

[Yahya]

try INDEX and MATCH

 

[s_richard]

try INDEX and MATCH

I've tried this but I can't seem to get the right combination or am doing something wrong... or it won't work for what I need it to do

Essentially the database could be 50 columns wide & 100 rows long & the unique ID could be sitting anywhere within this, regardless of where this ID is I then need it to return the value in the cell one column to the right...

Will index & match do this?

 

[Yahya]

could you post small sample from your data and the expected result to see what we can do for you ?

 

[s_richard]

could you post small sample from your data and the expected result to see what we can do for you ?

I'm having issues with my excel at the moment but essentially my database looks like this


WkSKU S WkSKU S WkSKU S
1-4567 10 2-4567 6 3-4567 8
6-3425 12 7-3425 7 8-3425 4

The values in the WkSKU columns are the unique identifiers, which is a combination of wk & SKU, the S columns represent sales for the Wk&SKU combination. This represents a manually created database off a series of data received by a 3rd party

Then on a separate sheet I have a random list of unique identifiers, there is no logical order of week or SKU number & it needs to stay this way & this list will change weekly

I then need to return the value in the sales column directly one cell to the right of the unique identifier

The separate page looks like the below, I need to fill in the sales related to that unique ID

WkSKU Sales

8-4567 ?
7-3425 ?
7-6785 ?
4-8967 ?
0-3271 ?
etc

Any help is much appreciated...

 

[Erik_Pgh]

Should look something like this:

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

<table style="width: 480px; height: 216px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:4571;width:94pt" width="125"> </colgroup><tbody> </tbody><tbody><tr style="height:17.25pt" height="23"><td class="xl66" style="height:17.25pt;width:94pt" width="125" height="23">Database is full Table (Including Headers)</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl65" style="height:14.25pt" height="19">ColumnSelect is the area where match is made (Number or Alpha)</td></tr></tbody>

</table>

 

[Peter_SSs]

Since your identifiers are unique and the value to be returned is numerical, you should be able to use SUMIF.

Formula copied down.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	WkSKU	S	WkSKU	S	WkSKU	S		WkSKU of Interest	S
2	1-4567	10	2-4567	6	3-4567	8		2-4567	6
3	6-3425	12	7-3425	7	8-3425	4		8-3425	4
4
SUMIF

 

[s_richard]

Since your identifiers are unique and the value to be returned is numerical, you should be able to use SUMIF.

Formula copied down.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	WkSKU	S	WkSKU	S	WkSKU	S	*	WkSKU of Interest	S
2	1-4567	10	2-4567	6	3-4567	8	*	2-4567	6
3	6-3425	12	7-3425	7	8-3425	4	*	8-3425	4
4	*	*	*	*	*	*	*	*	*
SUMIF

So I understand how to use the SUMIF to look for a unique ID however if we use a SUMIF don't we have to return the value from a predetermined column? ie. column E. The thing with my database is that the unique ID is always moving column & row, & no matter where this unique ID is I always need to return whatever value is one column to the right of this. So theorietically it could be any column between A-Z etc. If this is the case is there any way to tweak the SUMIF to make it work?

How in your formula do you get it to return the data one column to the right of the unique ID regardless of the unique ID & data being in different columns?

 

[s_richard]

Should look something like this:

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

<table style="width: 480px; height: 216px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:4571;width:94pt" width="125"> </colgroup><tbody> </tbody><tbody><tr style="height:17.25pt" height="23"><td class="xl66" style="height:17.25pt;width:94pt" width="125" height="23">Database is full Table (Including Headers)</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl65" style="height:14.25pt" height="19">ColumnSelect is the area where match is made (Number or Alpha)</td></tr></tbody>

</table>

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?

 

[Peter_SSs]

So I understand how to use the SUMIF to look for a unique ID however if we use a SUMIF don't we have to return the value from a predetermined column? ie. column E.

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.