formula to return cell values

[jag108]

Hi Guys\Gals

I am trying to return a value from a cell on one sheet when a match is made, then return the value from the cell adacent from a different sheet.

Trying to use matchbut not getting the value returned that I am after.

Once agin as I feel this is difficult just to try and explain.

Col1|col2

Match the value col1 return the value in col2 to the calling sheet.

Using Excel 2010 on XP Pro SP 3

 

[T. Valko]

Hi Guys\Gals

I am trying to return a value from a cell on one sheet when a match is made, then return the value from the cell adacent from a different sheet.

Trying to use matchbut not getting the value returned that I am after.

Once agin as I feel this is difficult just to try and explain.

Col1|col2

Match the value col1 return the value in col2 to the calling sheet.

Using Excel 2010 on XP Pro SP 3

Is this what you had in mind...

Book1
	A	B	C	D	E
2	A	East	_	Lookup	Result
3	B	South	_	F	West
4	C	North	_	_	_
5	D	West	_	_	_
6	E	North	_	_	_
7	F	West	_	_	_
8	G	East	_	_	_
9	H	West	_	_	_
10	I	East	_	_	_
11	J	North	_	_	_
Sheet2

This formula entered in E3:

=INDEX(B2:B11,MATCH(D3,A2:A11,0))

 

[jag108]

90% there, the data I am looking up is on a different sheet and not sorted.

This for hardware matching serial numbers to models.

 

[T. Valko]

90% there, the data I am looking up is on a different sheet and not sorted.

This for hardware matching serial numbers to models.

It doesn't have to be sorted and just include the appropriate sheet name.

Data on Sheet2, criteria cell on some other sheet.

=INDEX(Sheet2!B2:B11,MATCH(D3,Sheet2!A2:A11,0))

 

[shemayisroel]

90% there, the data I am looking up is on a different sheet and not sorted.

This for hardware matching serial numbers to models.

Further to Biff's solution, see below help on INDEX/MATCH

http://www.contextures.com/xlFunctions03.html

 

[jag108]

Nearly there, I can locate the value in the "stock" sheet, now I need to "return" the cell next to the matched value.

 

[T. Valko]

Nearly there, I can locate the value in the "stock" sheet, now I need to "return" the cell next to the matched value.

How about telling us EXACTLY where the data is that you're looking for. Be VERY SPECIFIC.

 

[jag108]

I knew this was going to be difficult from my side.

Serial numbers on "scan sheet", column B starting at row 4, scanned by hand scanner.

Values to match to "stock comparison" sheet, column B starting at row 1, value needed to be displayed in col 1 of the "scan sheet" is in col C on the "stock comparison" sheet.

This formaul is good, but I would like to return the value in column "C" of the "stock comparison" sheet
=INDEX('stock comparison'!B1:B12,MATCH(B4,'stock comparison'!B1:B12,0))

the above formula is on the "scan sheet" A4 and uses the value in "scan sheet" B4 to match on the 'stock comparison' sheet C1:C120.

I hope I have not confused the matter even further.

Thanks for putting up with me.

 

[T. Valko]

I knew this was going to be difficult from my side.

Serial numbers on "scan sheet", column B starting at row 4, scanned by hand scanner.

Values to match to "stock comparison" sheet, column B starting at row 1, value needed to be displayed in col 1 of the "scan sheet" is in col C on the "stock comparison" sheet.

This formaul is good, but I would like to return the value in column "C" of the "stock comparison" sheet
=INDEX('stock comparison'!B1:B12,MATCH(B4,'stock comparison'!B1:B12,0))

the above formula is on the "scan sheet" A4 and uses the value in "scan sheet" B4 to match on the 'stock comparison' sheet C1:C120.

I hope I have not confused the matter even further.

Thanks for putting up with me.

OK, try this...

=INDEX('stock comparison'!C1:C12,MATCH(B4,'stock comparison'!B1:B12,0))

 

[jag108]

Sweet!

With some tweaking I made this work

INDEX('stock comparison'!A1:C12,MATCH(B4,'stock comparison'!B1:B12,0),3)

With the help of this link.

http://www.excel-examples.com/index-match.htm

Thanks guys for seeing me through this.

*Resolved*