IF A1 true than value from cell B1

[ChristianSigge]

Hello,
I have a problem, maybe it's simple but I'm not able to solve this,
thats how my sheet looks like:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Jobnumber[/TD]
[/TR]
[TR]
[TD]Sandro[/TD]
[TD]A801336.00[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD]A801233.01[/TD]
[/TR]
[TR]
[TD]Sandro[/TD]
[TD]A802444.00[/TD]
[/TR]
</tbody>[/TABLE]



In Cell "J" I'm using this Formula to read out a specific value:

=IF(ROW(A2)-1<
COUNTIF(A:A;"Sandro");"Sandro";"")

<countif(a:a;"sandro");"sandro";"")[ quote]<countif(a:a;"sandro");"sandro";"")[="" code]<countif(a:a;"sandro");"sandro";"")<="" strong=""><countif(a:a;"sandro");"sandro";"")< strong="">
In cell "K" I want to read out the Value next to the Name, for example in this case I'm searching for the Name "Sandro" and I want to read out the job Number next to his name.
I tried it with offset,

=IF(ROW(A2)-1<
COUNTIF(A:A;"Sandro");OFFSET(A:A;1;1;1;1);"")

<countif(a:a;"sandro");offset(a:a;1;1;1;1);"")< strong=""><countif(a:a;"sandro");offset(a:a;1;1;1;1);"")< strong="">, it's not working correctly it read out the value of all listet Job numbers and not just the numbers next to the name "Sandro".


Thank you very much for your help.</countif(a:a;"sandro");offset(a:a;1;1;1;1);"")<></countif(a:a;"sandro");offset(a:a;1;1;1;1);"")<></countif(a:a;"sandro");"sandro";"")<></countif(a:a;"sandro");"sandro";"")[>

 

[arthurbr]

Why not use a simple VLOOKUP in cell K =VLOOKUP("Sandro",$A$2:$A$100,2,0) or an INDEX/MATCH combination

 

[ChristianSigge]

Why not use a simple VLOOKUP in cell K =VLOOKUP("Sandro",$A$2:$A$100,2,0) or an INDEX/MATCH combination

Hello, Thank you for your reply,
the Formula you posted doesn't work, Excel mention the following Error "Invalid cell reference error".
but if I change the "col_index_num" to "1" it works by showing the name "Sandro".

But I don't think that formular is the right one for my problem, my Excel sheet contains several cells with the name "Sandro"
and what my Formular does is to list all entries with "Sandro" :

K2 - value with sandro 1
K3 - value with sandro 2
K4 - value with sandro 3

and so on.