IF Statement to return variable outcomes

[Brebs69]

I would like some help if possible with an IF statement I am trying to develop.

What I am trying to do is search a range of cells (G4:G21) for a value, in this case 1000. When I find that value I wish to return a corresponding name & number to the second sheet of my workbook. So for example if 1000 is found in G10 then I would like to return the name in B10, & the number in F10 to the second sheet of my workbook. I assume I have to somehow modify the value_if_true part of the equation but I don't know how to.

This is the formula I am using, which lets me return a specific name (Sheet2!B4) but this is not always right.

=IF(COUNTIF(Sheet2!G4:G21,1000),Sheet2!B4,"")

Any help would be greatly appreciated. I have also attached the spreadsheet itself.

Aaron Sharpe Spreadsheet.xlsx

[benishiryo]

hi Brebs69, welcome to the forum. in sheet2!B4, try:
=INDEX(Sheet2!$F$4:$F$21,MATCH(TEXT(REPLACE($A4,LEN($A4)-1,0," "),"hhmm")+0,Sheet2!$G$4:$G$21,0))

for C5, simply change the red portion:
=INDEX(Sheet2!$B$4:$B$21,MATCH(TEXT(REPLACE($A4,LEN($A4)-1,0," "),"hhmm")+0,Sheet2!$G$4:$G$21,0))

[humdingaling]

issue is your front sheet and back sheet time formats dont match
attached amendment

b4 put

Please Login or Register  to view this content.

C4

Please Login or Register  to view this content.

[Brebs69]

Thanks for that, that's what I was trying to do.