Cell value from a grid

While we can use vlookup or hlookup to return a cell value in a particular row or column. Is there a way we can lookup from both row and column to return a particular value from a cell in a grid?

Consider the case below:

(Yes, I have added more colours to the table this time 🙂 )

The construct is such that every week 5 batches of measurement is carried out. The dates are given in cells A2:A14 and batch numbers are given in cells B1:F1.

Result table for measurement for specific dates and batches is given in the table below:

You could use one of the following formulas to arrive at the result:

Method1: vlookup with match

C17=VLOOKUP(A17,A2:F14,MATCH(B17,A1:F1,0),FALSE)

Of course, you could find the same result with hlookup with match. Only remember to lookup in a row instead of a column

Method2: Index with match

C18=INDEX(B2:F14,MATCH(A18,A2:A14,0),MATCH(B18,B1:F1,0))

Method3: Indirect with address and match

C19=INDIRECT(ADDRESS(MATCH(A19,A2:A14,0)+ROW(A2)-1,MATCH(B19,B1:F1,0)+COLUMN(B1)-1))

And now the bonus formula – using array

Method4: Sum as an array function

C20 {=SUM(($A$2:$A$14=A20)*($B$1:$F$1=B20)*B2:F14)}

Array formula is entered with ctrl+shift+enter

Method5: Use sumproduct as an alternate for array function

C21=SUMPRODUCT(($A$2:$A$14=A21)*($B$1:$F$1=B21)*B2:F14)

Use one of the above formulas or make one for yourself and share with others. Have fun 🙂

Read Full Post »

Lookup to the left

For looking up through vlookup, the value to be returned needs to be to the right of lookup_value. Here is a solution for returning a value to the left of lookup_value using match and offset functions

The problem is given in the image sheet below

 

The H1Y1 performance of certain sales people have to be returned. This is achieved through this formula in cell B10

Method 1: Match with Offset

B10 = OFFSET($C$2,MATCH(A10,$C$3:$C$7,0),-2)

This is then dragged to cover all sales people for which figures are required.

Note: match function returns the placement of lookup_value in the column, this is then used to offset. Note that offset reference (C2) is one cell above the lookup column (C3:C7) to ensure that if match returns 1, offset gives a correct value

Method 2: Sum as an array function

B10 {=SUM(($C$3:$C$7=A10)*$A$3:$A$7)}

entered with ctrl+shift+enter

Method 3: Sumproduct

B10=SUMPRODUCT(–($C$3:$C$7=A10),$A$3:$A$7)

May you find this useful!

Read Full Post »

Handling errors in excel

I have listed down the types of error in excel and their likely causes in the table below:  

 

 Error type can be found out through the formula =Error.Type(cell_ref)

Count instances of error

One of the ways to count the total occurence of error in an area is this

{=SUM(IF(ISERROR(B2:B8),1,0))} entered as an array formula with ctrl+shift+enter

In the above example it returns 7

Replace error with a dash

If a formula gives you an error for certain input parameters and you do not want these to show up on excel sheet, you could use this function

=IF(ISERROR(formula),”-“,formula)

We have used this in creating spaces within a list using indirect function

Let me know if you need any help with these errors. Have fun handling errors 🙂

Read Full Post »

Cows and Bulls

Here is how you can play the game Cows and Bulls in excel

The objective of the game is to guess the secret number (which is hidden from the player). The player can guess the number as many times. If the matching digits are on their right positions, they are “bulls”, if on different positions, they are “cows”. Example given below

The formula for cows:
C3 {=COUNT(MATCH(MID(B3,ROW($1:$4),1),MID($B$1,ROW($1:$4),1),0))}
(entered as an array formula, ctrl+shift+enter)

The formula for bulls
D3 {=SUM(IF(MID($B$1,ROW($1:$4),1)=MID(B3,ROW($1:$4),1),1,0))}
(entered as an array formula, ctrl+shift+enter)

Drag the formula down. You could keep entering your guesses in column B

One could also highlight the cell if the number is guessed correctly, through conditional formatting
Click B3 and through Format>Conditional Formatting…

Copy the format to all cells in column B through Format Painter brush on the toolbar

Tip: If you are playing the game alone and have no one to give you a 4 digit number, you could use rand() function

In the secret number cell enter = ROUND(RAND()*10^4,0), Press F2 and F9 – this would freeze the random number. Make sure that no digits are repeated and the random number is 4 digits long. You can use len function and check duplicate function for that

Have fun playing the game 🙂

Read Full Post »