CODEWITHSUNDEEP
excelexcel-formula
Rebecca Peel Joanne
Rebecca Peel Joanne

Reputation: 11

Find the first occurrence of a value in a row and return cell next to it

I want to find in a row the first occurrence of "Sick" and get the date on the right next to it. Any ideas?

We have tried this, but believe that there is an easier way:

=SI.ERROR(INDICE(D2,COINCIDIR("Sick",C2,0)),SI.ERROR(INDICE(F2,COINCIDIR("Sick",E2,0)),INDICE(H2,COINCIDIR("Sick",G2,0))))

In English:

=IFERROR(INDEX(D2,MATCH("Sick",C2,0)),IFERROR(INDEX(F2,MATCH("Sick",E2,0)),INDEX(H2,MATCH("Sick",G2,0))))

I want to find an easier method, because I have to do this for several tables with different dimensions

Upvotes: 1

Views: 11719

Answers (1)

Dominique
Dominique

Reputation: 17493

When everything is in one row:

    A     B             C       D             E     F             G
Row 2    ok    10/04/2019    sick    11/04/2019    ok    12/04/2019

Then following formula solves your issue:

=INDEX(B2:G2;1;MATCH("sick";B2:G2)+1)

This means : in B2:G2, verify if "sick" can be found, and in case yes, take the column next to it (hence the +1 as MATCH() returns the column number).

When everything is in one column:

This is exactly the point of the VLookup() function: look at following example:

1      A             B            C
2                title          Date
3                   ok    10/04/2019
4                 sick    11/04/2019
5                   ok    12/04/2019

Use the following formula:

=VLOOKUP("sick";B1:C4;2)

This yields the value 11/04/2019 (after the correct cell formatting, of course).

Meaning: look for the value "sick" in the table B1:C4, and use the corresponding value for the 2nd column (containing the requested date).

Upvotes: 4

Related Questions