CODEWITHSUNDEEP
excel
zackrspv
zackrspv

Reputation: 170

INDEX/Match and VLOOKUP Assistance

So, I have a table, that looks like the following:

table

What I want to do: Return the name of the person who has the orange block for the current day.

What I can do: =MATCH(EDATE(NOW(), 0), B9:AQ9,0)

Which, since today is the 16th, returns Column 18.

So, I'd want to search for any orange block in column 18. An orange block is orange because it has the text x in it.

Only, I do not know how to search with just that column only.

Once I get the row number of that column, I'll then need to be able to return an INDEX() lookup of the COLUMN and ROW in the first column A to return the name of the person.

Name column is Column A - and the last column in that list is AQ.

The row B9:AQ9 is where the day numbers are; these are actually dates, just formatted only to return the day portion.

So, I know how to find the column in question, that's with the code above. I'm just not sure how to REFERENCE that column and match any "x" in that column to return the ROW.

In this case, for 1/16/2019 (the yellow highlighted column there), it would return Krystle, as the lookup should return Column 18, Row 6

Upvotes: 1

Views: 43

Answers (2)

tfehring
tfehring

Reputation: 394

Based on the MATCH formula you provided, you can get the column corresponding to that date (as an array) using this formula:

=INDEX($B$10:$AQ$13, , MATCH(EDATE(NOW(), 0), $B$9:$AQ$9, 0))

Then you can MATCH a name based on the values in that array:

=INDEX($A$10:$A$13, MATCH("x", INDEX($B$10:$AQ$13, , MATCH(EDATE(NOW(), 0), $B$9:$AQ$9, 0)), 0))

Upvotes: 1

Solar Mike
Solar Mike

Reputation: 8415

I'm assuming your column 18 is column T for the following,

Based on what you say, this looks for an "x" in col T and returns the name from Col A in the same position:

=INDEX(A2:A5,match("x",T2:T5,0))

An example, using different columns: enter image description here

Upvotes: 0

Related Questions