Excel to lookup and return data on worksheet

[cinox]

Hi,

I`ve made a worksheet with an overview of my sheeps at the farm:

Sheep nr: 1111 Lamb nr: 9999

And i`ve made a new worksheet within a tab on my existing worksheet for the lambs.

And i have like:

Lamb nr: 9999 and i have Mother of lamb: <- and here i want word to automatic look up the first worksheet and return with sheep nr that matches lamb nr: 9999.

Someone know what function i can use and how i can do it?

Thanks

 

[VoG]

Take a look at INDEX/MATCH http://www.contextures.com/xlFunctions03.html

 

[cinox]

Thank you seems like this is what i need

But i have a problem... My reference is lets say A15 and value in A15 is: 1101
But in the match colum my reference has like 1101 , 1102 , 1103 and my function returns N/A. If i change reference in function to *1101* then it works.
But then it wont work for the other cells. Is it possible to use something like *A15* ????

how could i resolve this?

Take a look at INDEX/MATCH http://www.contextures.com/xlFunctions03.html

 

[VoG]

It would be better to split entries like 1101 , 1102 , 1103 into separate columns. You can do this by selecting the column, Data > Text to Columns, specify Delimited with , as the delimiter.

 

[cinox]

I can`t do that.. because the "Match" function can only look after match in one row for example $D$ if i trye $D$:$E$ it gives me N/A.
Then i have to have 3 numbers in one colum. Thats why i need something like Reference colum: *A15* .... "*1101*" works but not *A15* is there any way to do this??

 

[VoG]

Maybe "*"&A15&"*"

 

[cinox]

thanks your the man

anyway do you know if its possible to have "Match" function to check two colums like C&D for the value only get it to work with one colum.

regards,

sindre

 

[VoG]

You would need to use (pseudo formula)

=IF(ISNA(formula to look in column C), formula to look in column D,formula to look in column C)

 

[cinox]

ok, so if i have this formula now:

=INDEX($A$4:$A$30;MATCH(A18;$D$4:$D$30;0)

and want to add also E4 : E30
how should it be then?

thx

 

[VoG]

Try

=IF(ISNA(MATCH(A18;$D$4:$D$30;0));INDEX($A$4:$A$30;MATCH(A18;$E$4:$E$30;0));INDEX($A$4:$A$30;MATCH(A18;$D$4:$D$30;0)))