0

enter image description here

Hi,

I am hoping for some help in a V look up when using two drop downs for todays date (G1 & G2).

The table on the right are calls made each day will contain data of calls made by employees, it will be a table for the entire year. the table range will be Q58:AE423.

The formula needs to use the information selected in the drop down to the left columns of the table to match date and select the correct initials.

The two drop downs on the left would be used to select todays date and then the calls made today green boxes would look up the data. For example, if it was Jan 1st, NL would show as 9.

Any help would be much appreciated. Thank you,

2

If you have XLOOKUP available, you can do this:

enter image description here

=XLOOKUP($B$1&" "&$B$2,$I$2:$I$32&" "&$J$2:$J$32,$K$2:$N$32,"")

However, why not just change the tables to be a single date parameter and a date as the row header for the table? This would make it much simpler, in my opinion.

enter image description here

Here, I have used this formula to create the list of dates (in cell F2):

=SEQUENCE(31,1,"1/1/2020")

If your version of Excel doesn't have this function, you can create the list manually without much effort.

This is the XLOOKUP formula:

=XLOOKUP($A$2,F2#,$G$2:$J$32,"")

You can replace with appropriate VLOOKUPs in each column if you don't have XLOOKUP. For example, for CP:

=VLOOKUP($A$2,$F$2:$J$32,2,FALSE)

In cell A2 I used Data>Data Tools>Data Validation, configured like this (i.e. only allow the user to select a date from the list on the page):

enter image description here

The custom cell format for cells A2 and the list of dates in column F is this:

ddd, mmm dd, yyyy

The nice thing about XLOOKUP is you only need to enter one formula in the first column. It can return more than one column from the matched row in the main table.

1

FlexYourData has a good point about it being a bad idea to have your date in your table broken into 2 columns. ONE of the reason is that in order for VLOOKUP to work, it indexes based on the first column of the table only, not the first two.

VLOOKUP as the data is presented currently will not work, but there are 3 potential options listed below:

1) Helper Column

Place a helper column either to the left or right of the date columns in your reference table. In the helper column concatenate the Month and the Day. This column can be hidden for presentation purposes. When performing your VLOOKUP concatenate your to reference dates, and your reference look up table starts with your Helper column

Helper column to the right of dates
=VLOOKUP(MONTH&DAY,TABLE,MATCH(INTIALS,INITIALS_RANGE,0)+1,0)
or
Helper column to the left of dates
=VLOOKUP(MONTH&DAY,TABLE,MATCH(INTIALS,INITIALS_RANGE,0)+3,0)

POC

2) Replace the dates

In your reference table you can replace your dates with either a single date column as suggested by FlexYourData or leave it as two date columns that each contain an excel date. I will describe the later in more detail. The problem here is that you will lose the ST,ND,RD,TH on the day in the reference table. IF this is important than this method will not work

In your column enter 20/01/01 (whatever matches your date format for the first day of the year) in both the MONTH cell and the DAY cell, or enter it in one and make the other equal the first with a formula.

Then custom format the cells. Use the formatting of MMM for month and D for day Now since the full date is actually contained in each cell, you only need to reference day column when defining your lookup table.

the next hurdle will be converting your lookup date into a date. This can either be achieved by entering a full date instead of separate values, or you can combine the separate values to make a date. To make a date out of separate values you can use the DATE function as follows:

=DATE(YEAR,MONTH,DAY)

So since you do not have a year, you can manually set that to match the year you used when your set up your reference table, or you can add it as an additional cell as part of the look up, or your can pull if from the first date cell in your table. For the later case, if you first table date was in cell L4 you could do the following:

=YEAR(L4)

To get the month (Assuming in D4) you can opt for the following formula to convert from text to a number:

=MATCH($D$4,{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0)

To get the day as a number the preferred method is to have it as a number with no "st", "nd", "rd", or "th" after it. how ever if you do have to strip the number out then you could use the following formula with your reference days assumed to be in D5.

=LEFT($D$5,len($D$5)-2)

Now that you have all three values, insert them into the date formula and you wind up with something that looks like the following:

=DATE(YEAR($L$4),MATCH($D$4,{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0),LEFT($D$5,len($D$5)-2))
or
Hard Coded Year
=DATE(2020,MATCH($D$4,{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0),LEFT($D$5,len($D$5)-2))

So assuming your reference table is L3:R34 your vlookup would become:

=VLOOKUP(DATE(2020,MATCH($D$4,{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0),LEFT($D$5,len($D$5)-2)),$N$3:$R$34,MATCH(INTIALS,INITIALS_RANGE,0)+1),0)

Note the look up range is actually N4:R34 since you are ignoring the month column.

POC2

3) USE Alternate Formula

Someone else mentioned used COUNTIFS. That is one option and works great for numbers. Not so great if for some reason your needed to return text value. The combination of INDEX and MATCH will work for you.

MATCH will be used twice. once to determine the appropriate row and once to determine the appropriate column (eluded to in options above). MATCH will return the position of what your a looking for in a range/list. Tell it what you want to search for, what the range/list to look in is and what type of match you want to make.

so to find your initials in the assumed the reference table in L3:R34 with row 3 being the initials and the initials you want to look up are in A10 then your match formula would be:

=MATCH(A10,$N$3:$R$3,0)

Notice 3 things. The first is I locked the range reference to avoid having the cells change as I copy the formula. The second is I started the range at the first initial instead of the edge of the table. The third is that the result is the position if the range and NOT the column of your spreadsheet NOR the column of your reference table.

For VLOOKUP you need to align the result to give the column of your table. You will need to manually add the number of extra columns before the initials. In your case it is 2, so the formula becomes:

=MATCH(A10,$N$3:$R$3,0)+2

Now to match the row it is a bit trickier and uses an array formula. Concatenate the Month (D4) and Day (D5) you are looking for and for the look up range, Concatenate the two ranges. You formula becomes

=MATCH($D$4&$D$5,$L$4:$L$34&$M$4:$M$34,0)

now the above is an array formula so you will need to use CONTROL+SHIFT+ENTER instead of just ENTER when confirming your formulas. You will know you have done it right when { } show up around your formula in the formula bar. note that they cannot be added manually.

The the last part is using index to define the table you want to look in. Unlike VLOOKUP, you only want to include the reference data and not the header rows and columns. This also means that the results from the MATCH formulas will align with the data without ajustments. So with the assumed reference table in L3:R34 your index function will look like this:

=INDEX($N$4:$R$34, ROW TO LOOK IN, COLUMN TO LOOK IN)

when you substitute you match formulas in it becomes:

=INDEX($N$4:$R$34,MATCH($D$4&$D$5,$L$4:$L$34&$M$4:$M$34,0),MATCH(A10,$N$3:$R$3,0))

REMEMBER it is an array formula so need the special CSE formula confrimation instead of just Enter.

POC3

0

You can use COUNTIFS to apply multiple criteria to count records in the table.

Just add each column Month H:H Day I:I Name J:J and the referenced cell it must match Month G1 Day G2 Name A5:A...

=COUNTIFS(H:H,$G$1,I:I,$G$2,J:J,A$5)

For a dynamic solution, name the ranges and/or create a table, then you can use something like this:

=COUNTIFS(data[day],dd_day,data[month],dd_mo,data[name],FILTER(results[#Headers],COLUMN(results[#Headers])=COLUMN(),""))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.