Free Microsoft Excel 2013 Quick Reference

2 dimensional lookup

In the spreadsheet attached I have two worksheets A and B . A has the raw data

(a) - Based on the raw data . Values should be in returned in C3 of B worksheet using the lookup formulas . It should look for C2 from Column A of A worksheet & Match it with B3 from Row A of A worksheet .

Incase if there are no values , it should return zeros .

(b) In worksheet B , Cell C2 has the data validation list , each time I update the sheet A I need to manually change the range . Can I get a formula , so that the range is automatically adjusted based on the data in worksheet A .

Thank you


Post your answer or comment

comments powered by Disqus
Currently I am using Excel 2003 and trying to compile a VBA macro so that I may have more than 3 conditional formats. Right now I am using the following formula's in my conditional format. I am trying to take this formula below and convert it in to VBA. I just recently became familiar with VBA so I'm at a basic level for programming in this language

Conditional Format 1
=IF(VLOOKUP(Operator,JanJunOperator,MATCH(DATE(YEAR(J6),MONTH(J6),DAY(J6)),JanJunDate,0),FALSE)="S", TRUE,FALSE) --- This will be "YELLOW"

Conditional Format 2
=IF(VLOOKUP(Operator,JanJunOperator,MATCH(DATE(YEAR(J6),MONTH(J6),DAY(J6)),JanJunDate,0),FALSE)="D", TRUE,FALSE) --- This will be "PINK"

Conditional Format 3
=IF(VLOOKUP(Operator,JanJunOperator,MATCH(DATE(YEAR(J6),MONTH(J6),DAY(J6)),JanJunDate,0),FALSE)="N", TRUE,FALSE) --- This will be "GREEN"

** JanJunOperator & JanJunDate is a range of cells from another worksheet
*** Operator is the name of a person

I need to take this formula and create a VBA macro so that I could obtain 5 condintional formats

The hardest thing for me right now is figuring out how to take the "2 dimensional lookup" (Vlookup & Match) and converting this into a VBA code.

I'd appreciate all the help I can get

If my coding doesn't explain what I want to do, I could explain further

Excel has useful lookup functions, which allow for looking up values in specified rows or columns of an array, based on matching criteria in the irst row or column (VLOOKUP and HLOOKUP). Basically a one dimensional look-up and then counting down to the row number specified to select the value in that CELL

I need a 2 dimensional look-up. For example in the following matrix

I have listed in the first column a label (e.g. specific items bought)
And in the first row a label (e.g. person)
For each item I have the amount of money spent on buying the item by each person.

John Frank Peter
CD-s 200 300 100
Videos 50 400 10
DVDs 250 350 500

Using the RANK function can rank the matrix on highest expenditure i.e.

John Frank Peter
CD-s 6 4 7
Videos 8 2 9
DVDs 5 3 1

The lookup function I need would help me to find the name based on the ranking i.e.
Name
1 Peter
2 Frank
3 Frank
4 Frank
5 John
6 John
7 Peter
8 John
9 Peter

How do a formulate the function to extract the names based on the lookup reference (rank) in the first column of the last table??

If you know, please tell me.

Regards,

Cees

Hello,

I would like to return the address of a lookup value in a 2 dimensional array.

As an example (where capitals are columns and numbers are rows):

_|A B C D
1|h i j k
2|l m n o
3|p q r s
4|t u v w

I'm looking for a way to say: "Where is 'r' located in the range A1:D4" and the answer to be "C3"

Any thoughts much appreciated.

Many thanks,

Mark

Hello all.

Can someone please help me here.

As you can see in the attached image, I have a 2-Dimensional table.
When a users selects a given Priority (High, Medium, or Low) and a Frequency (Routine, Periodic, or Occasional), I want the appropriate result to be returned e.g.

If user selects HIGH as priority and Periodic as Frequency, then he should get 11 .00 as the answer.

Will appreciate if someone can come to the rescue.... I think this calls for a combination of VLOOUP and HLOOKUP but this is where I get stuck

Many thanks in advance

If you are having trouble performing a lookup based on 2 criteria and your base data array where you are performing the lookup may not always be sorted in the same order as the source values you are trying to match, I found this solution to work: http://support.microsoft.com/kb/275170.
I hope this helps someone else, I spent days on this problem!
Cheers form Guelph!

Here is what I need....can you help?

I have a table of values
Names 1 2 3 4 5
Jack 10 12.5 15.6 19.5 24.4
Jan 45 56.3 70.3 87.9 109.9
John 900 1,125.0 1,406.3 1,757.8 2,197.3
Jane 150 187.5 234.4 293.0 366.2

On another sheet, I will have a name that corresponds with the row and a value that will fall somwhere in the row...

For Example, John & 313

I need to know how one formaula can dynamically look at the table and know, I have changed the name and the dollar amount and will tell me whether it corresponded to 1, 2, 3, 4, 5, etc...

Hope this makes sense and someone will have an idea.

I have considered Vlookup, index, match, etc, but have yet to come up with a solution.

Thanks

HOW TO: Perform a Two-Dimensional Lookup in Excel 2000

This article describes two methods to perform a two-dimensional lookup; one method uses the INDEX and MATCH worksheet functions and another method uses natural language formulas.

Hello,

I am trying to write a public function that fills a table for a 2-dimensional array and am having trouble with my named ranges. The x-axis is based on years (range F2:O2) and the y-axis is a q_factor (range E3:E23) so the data range would be (F3:O23. The following outlines my logic:


	VB:
	
 
Public Function bondValue(years As Range, q_factor As Range, z As Double) 
     
    Dim nRow As Long, nCol As Long 
    Dim bondPort As Range 
     
    nRow = q_factor.Rows.Count 
    nCol = years.Columns.Count 
    sumTau = 0 
     
     'Attempt at setting the bondPort to the proper range
    Set bondPort = Range("f3", [nRow,nCol]) 
     
     'This is the logic that I am aiming for
     
     'For Each i = 3 to nrow
     'For each j = 3 to nCol
     ' in bondPort(x[i], y[j])
     'While sumTau < x[i]
     'tau = z * Log(rand())
     'sumTau = sumTau + tau
     
     'If sumTau < x[i] < x[i] Then
     'bondPort.value = insert function here
     'z = y[j] * z
     
    End 
Next j 
Next i 
bondValue = bondValue + bondPort.Value 
 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As always, any help is greatly appreciated.

Thanks,

Tesa

I can set up a 2 dimensional array by using

array = Workbooks("Workbook.xls").Worksheets("Data").Range("D_all")

as range D-all contains 2 columns and 20 rows

I can send that list to a combo box by using

Me.ComboBox1.List() = array

If I have ColumnCount set to 2 then both columns will be listed

If I have ColumnCount set to 1 then the 1st part of the array will be listed

But how do I list just the 2nd part of the array

This formula takes care of what needs to happen after the hire date in cell O2
When I place y in cell K3 the cell this formula is in goes blank that is right. if I put an N in K3 it gives me what I expect the answer to be according the quarters in the year.

=IF(DATEDIF(O2,TODAY(),"y"),MIN(INT(DATEDIF(DATE(YEAR(O2)+1,FLOOR(MONTH(O2)-1,3)+1,0),TODAY(),"m")/3)*(K3"y")*0.005+0,0.03),0)*I27

Here is the problem
I have a question "Has this driver had an accident within the past Quarter?"
That question is answered at cell K3. With the "y" or the "n" Then in cell O3 is the Date of that accident. If the answer is "y"
That date will remain until the date of the next accident
It is at this date the formula must disregard the hire date at cell O2.
1 calender quarter must pass after the date in cell O3 before the bonus can restart.

If there is never a date placed at cell O3 the formula must use the hire date at cell O2.

I think I have too many nested arguments in this formula.

I just wonder if this needs to be a 2 part LOOKUP formula?
If it is I have no idea how to write it.
I would be truly greatfull if someone could help

I was working on a simple drawing program in excel, but I have two major problems.
1.My actual loop for drawing doesn't do anything and I can't figure out why. this is just for drawing down, if get this right I should be able to change it on my own to go right left and up.

Option Explicit

Dim mintRow As Integer
Dim mintColumn As Integer
Dim mintHistoryRow As Integer
Dim mintColor As Integer

Private Sub cmdDown_Click()
mintRow = 3
mintColumn = 6
mintColor = 1
Range("A1").Interior.ColorIndex = mintColor
Dim vntSteps As Variant
Dim strDirection As String
vntSteps = InputBox("How many steps would you like to move downwards?", "Downward Steps", "1")
strDirection = "Down"
If IsNumeric(vntSteps) = False Then
MsgBox "Error", vbOKOnly, "Error"
Exit Sub
Else
For intCounter = mintRow To vntSteps
Cells(mintRow, mintColumn).Interior.ColorIndex = mintColor
Next intCounter
End If

My second problem is that I want to be able to save the "drawing". I've been trying to use a module-level 2 dimensional array which I declared(the drawing space is 20x20):

Dim mintDrawing(19,19) As Integer

I can't get the nested loop for saving each cells ColorIndex though, I'm just not sure where to start with that at all.

Any help would be greatly appreciated.

Thanks,
Connor

Master list of data
DR AB TT
1 X1 n1
2 X2 n12
3 X3 n23
4 X4 n34
5 X5 n45
1 X2 n2
2 X3 n13
3 X4 n24
4 X5 n35
5 X1 n41
1 X3 n3
2 X4 n14
3 X5 n25
4 X1 n31
5 X2 n42
1 X4 n4
2 X5 n15
3 X1 n21
4 X2 n32
5 X3 n43
1 X5 n5
2 X1 n11
3 X2 n22
4 X3 n33
5 X4 n44

My data list:
AB DR TT
X1 1
X1 2
X2 3
X4 3
X3 4
X2 1
X5 2

How to fill column TT based on the master list?

I have a masterlist of data, which contains 3 columns (say A, B, C) and 25
rows. I need to get the value of the cell in column C only when the
corresponding cells in Column A and column B are matching.

Ex:Master List
DR AB TT
1 X1 n1
2 X2 n12
3 X3 n23
4 X4 n34
5 X5 n45
1 X2 n2
2 X3 n13
3 X4 n24
4 X5 n35
5 X1 n41
1 X3 n3
2 X4 n14
3 X5 n25
4 X1 n31
5 X2 n42
1 X4 n4
2 X5 n15
3 X1 n21
4 X2 n32
5 X3 n43
1 X5 n5
2 X1 n11
3 X2 n22
4 X3 n33
5 X4 n44

My Data List:
AB DR TT
X1 1
X1 2
X2 3
X4 3
X3 4
X2 1
X5 2

I want to fill the column TT using the master list

I've created a table similar to a mileage chart to score different shop
performance figures

Eg.

*Score*, *Revenue, Sales per opening hr
R, SPOH*,
A, 1000, 5,
B, 800, 4,
C, 700, 3,
D, 600, 2,
E, 500, 1,
F, 400, 0,

There are 6 "score grades" A to F & 11 key performance indicators (each
with their own unique abreviated header - e.g. SPOH)

I want to return a score for each store for each indicator depending on
each stores result.

Eg.

Shop 1 - Revenue is 550 - Score for this would be E as the value is
greater then 500 & less then 600.
Shop 2 - Revenue is 800 - Score for this could be B as the value is
greater then/= to 800 & less then 1000.

Would it be the INDES & MATCH function i'd need to use for this?? I
can't figure out how i'd use that to return a grade A to F?

--
loscherland
------------------------------------------------------------------------
loscherland's Profile: http://www.excelforum.com/member.php...fo&userid=6709
View this thread: http://www.excelforum.com/showthread...hreadid=533663

Hi Randy:

Is it column D or column B? In any event, you can sort from the menu using
Data | Sort and specify which column you want to sort on. Perhaps I'm
missing the point.

Regards,

Vasant.

"Randy S" > wrote in message
...
> How do I sort a 2-dimensional array from a formula? If I
> have 2 columns of data:
>
> Col A Col D
> Joe Smith $4.24
> Jane Doe $2.48
> Fred Flint $8.88
> John Stein $1.89
>
>
> I want to sort both columns based on values in Col B, so
> I'd end up with:
>
> Col A Col D
> Fred Flint $8.88
> Joe Smith $4.24
> Jane Doe $2.48
> John Stein $1.89

Hi guys,

I have a 2 dimensional array, Positions(x,y), which has a variable
number of rows(x) and columns(y):

e.g. 4 5 3 4
500 500 800 100
450 450 750 50
400 400 700 0
350 350 -50
300

I know the number of columns and the number of rows in each column
(stored in header of column).

How can i obtain permutations of all values in each column.... i.e.
500 500 800 100
500 500 800 50
500 500 800 0
500 500 800 -50
500 500 750 100
.....
.....
.....
350 300 700 0
350 300 700 -50

I am using VBA and outputting the values to fields in excel.

Does anyone have any sample code or ideas on sorting a 2 dimensional arrray. My array contains numers only (no strings) and I need it sorted numerically according to the 1st column.

Thanks

Russ Siebert

Can I create a 2 Dimensional array? If not, what is the easiest way to store a set of strings which can be any number of columns / records?

Cheers,
Chris

(sorry if the info is elsewhere, I do try searching before I post!)

I was working on a project recently where I had to convert a string into to a 2 dimensional array. I'm not saying this is the best way, but its the way I ended up doing it

When calling the function, just specify the string, the number of columns you want the resulting array to have and the seperator to use for converting the string to a 2 dimensional array. The default seperator is a space character, but could be anything you want. It will calculate the required number of rows.


	VB:
	
 
Option Base 0 
 
Public Function Str_2d(str As String, intCol, Optional Delim As String = " ") As Variant 
     
     'convert a string to a 2 dimensional array - using space as default delimiter.
     
    Dim Num_Rows As Long 
    Dim arrTemp, arrTemp2 
    Dim iCount As Integer, Row_Count As Integer, Col_Count As Integer 
     
    Num_Rows = Application.RoundUp((Len(str) - Len(Replace(str, Delim, "")) + 1) / intCol, 0) 'determine size and shape of
resulting array - number of rows for number columns
    arrTemp = Split(str, Delim): iCount = 0 'icount is the index for arrtemp and that is a zero based array
    Redim arrTemp2(Num_Rows - 1, intCol - 1) 
    For Row_Count = 1 To Num_Rows 
        For Col_Count = 1 To intCol 
            arrTemp2(Row_Count - 1, Col_Count - 1) = Trim(arrTemp(iCount)) 
            iCount = iCount + 1 
            If iCount > UBound(arrTemp) Then Exit For 
        Next 
    Next 
    Str_2d = arrTemp2 
     
End Function 
 
Public Sub driver() 
     
    Dim x 
    ActiveSheet.Cells.Clear 
    x = Str_2d("This is a sweet function for 2 dimensional arrays Ha! Ha", 3) 
     'or
     'x = Str_2d("This is a sweet function^for 2 dimensional arrays^Ha! Ha", 3, "^")
     'or
     'x = Str_2d("This is a sweet,function for 2,dimensional,arrays,Ha! Ha", 1, ",")
     'display the result....
    ActiveSheet.Range("A1").Resize(UBound(x, 1) + 1, UBound(x, 2) + 1) = x 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Hope someone finds it useful.

Ger

I've created a table similar to a mileage chart to score different shop performance figures

Eg.

Score, Revenue, Sales per opening hr
R, SPOH,
A, 1000, 5,
B, 800, 4,
C, 700, 3,
D, 600, 2,
E, 500, 1,
F, 400, 0,

There are 6 "score grades" A to F & 11 key performance indicators (each with their own unique abreviated header - e.g. SPOH)

I want to return a score for each store for each indicator depending on each stores result.

Eg.

Shop 1 - Revenue is 550 - Score for this would be E as the value is greater then 500 & less then 600.
Shop 2 - Revenue is 800 - Score for this could be B as the value is greater then/= to 800 & less then 1000.

Would it be the INDES & MATCH function i'd need to use for this?? I can't figure out how i'd use that to return a grade A to F?

I have a 2 dimensional array that holds an error code and a value associated with the code.

ReDim ErrorCodeArray(LearnerCount - 1, 9 To 32)

I am populating this ok.

I am having a problem extracting the maximum value in the data set.

'for each learner row that exists
For arrayLoop = 0 To LearnerCount - 1
'navigate down this column
For Each percentRange In Range("AH11:AH46")

'get the maximum value from the array
Pval = WorksheetFunction.Max(ErrorCodeArray())
'populate the column
Worksheets("Auditchecklist").Range(percentRange.Offset(arrayLoop, 0).Value) = Pval
'move down a row in the column
Next percentRange
'move to the next row in array
Next arrayLoop

this: Max(ErrorCodeArray()) returns the max value from all the array

if i try ErrorCodeArray(arrayLoop)

it squeels for more data

if i put ErrorCodeArray(arrayLoop, another value)

it only returns one value

How do I pass one whole row ie max(ErrorCodeArray(arrayLoop, 9 to 32))

Re : Excel Formulation of 2-Dimensional Array

1. Look at the formula, =COLUMN(INDIRECT("A:O")) ; It produces a
horizontal (1-dimensional) array :
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}. That looks nice.

2. And now, the question is, How is it possible to devise a formula to
give rise to a 2-dimensional (3-row x 5-column) array such as :
{1,2,3,4,5;6,7,8,9,10;11,12,13,14,15} ?

3. Please share your experience.

4. Regards.

Re : Excel Array Conversion from 2-Dimensional to One

1. Enter an Excel worksheet.

2. Enter into the range A1 : C1 = A, C, E.
3. Enter into the range A2 : C2 = B, U, T.
4. Enter into the range A3 : C3 = C, O, P.
5. Enter into the range A4 : C4 = D, I, G.
6. Enter into the range A5 : C5 = E, L, F.

7. Enter a formula into cell D1 = COUNTIF(A1:C5,A1:C5).

8. Partial evaluation of the formula in the formula bar shows that cell
D1 ={1,2,2;1,1,1;2,1,1;1,1,1;2,1,1} (i.e., 2-dimensional array).

9. Now, the question is, how to transform the formula in cell D1 such
that it would evaluate to D1 = {1;2;2;1;1;1;2;1;1;1;1;1;2;1;1} (i.e.,
One-dimensional array).

10. Please share your comment.

Hello All. Just registered as I stumped by this problem and need to open it up to the wider community! I can't post the sheet i'm afraid due to confidentiality restraints.

Here's the premise;

I have a spreadsheet with multiple worksheet tabs - one for each month of the year.

Within those tabs I have a series of identifier codes in column A, then a date range for the month as the row headers - e.g.

A B C D 1 02/04/12 03/04/12 04/04/12 2 CODE1 Text 3 CODE2 Text 4 CODE3 Text 5 CODE4 Text

I'm trying to pull a summary worksheet tab together to allow me to use the CODEx as a reference point to automatically pull through the Text across an entire years date range.

I can't get Vlookup/Match or Index/Match to work as the match command simply references cell B2 in each tab (for example) rather than actually 'matching' the contents of the cell to differentiate betweeen say 4th April 2012 and 4th May 2012.

Any ideas....

Thanks

Chris

Hi guys,

I have a 2 dimensional array, Positions(x,y), which has a variable
number of rows(x) and columns(y):

e.g. 4 5 3 4
500 500 800 100
450 450 750 50
400 400 700 0
350 350 -50
300

I know the number of columns and the number of rows in each column
(stored in header of column).

How can i obtain permutations of all values in each column.... i.e.
500 500 800 100
500 500 800 50
500 500 800 0
500 500 800 -50
500 500 750 100
.....
.....
.....
350 300 700 0
350 300 700 -50

I am using VBA and outputting the values to fields in excel.


No luck finding an answer? You could always try Google.