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

(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

- More than 3 conditional format using VBA & a 2 dimensional lookup
- Wanted: 2 Dimensional Excel Lookup formula
- Return address of value in 2 dimensional array
- 2 Dimensional Table Lookup
- TIP: Performing a 2 dimensional lookup in Excel 2000 onwards
- Formulas: strange 2 dimensional lookup
- HOW TO: Perform a Two-Dimensional Lookup in Excel 2000
- Function For 2-dimensional Array
- Fill ComboBox With 2 Dimensional Array
- IFDATE Formula Or Maybe 2 part LOOKUP Formula
- Loop trouble and 2 dimensional array problem
- 2-dimensional lookup
- How to do a 2-dimensional lookup?
- 2 Dimensional Lookup by column & rows to return score grade
- Sorting a 2-dimensional array
- Permutation of values in 2 dimensional array
- Sorting a 2 dimensional array??
- 2 Dimensional Array?
- String to 2 Dimensional Array - VBA Function
- 2 Dimensional Lookup by column & rows to return score grade
- Data from 2 dimensional array
- Re : Excel Formulation of 2-Dimensional Array
- Excel Array Conversion from 2-Dimensional to One
- Multi Worksheet, 2 Dimensional Lookups - help appreciated!
- Permutation of values in 2 dimensional array

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

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

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

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

I hope this helps someone else, I spent days on this problem!

Cheers form Guelph!

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

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.

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:As always, any help is greatly appreciated.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] Then 'bondPort.value = insert function here 'z = y[j] * z End Next j Next i bondValue = bondValue + bondPort.Value End FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks,

Tesa

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

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

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

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?

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

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

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

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.

Thanks

Russ Siebert

Cheers,

Chris

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

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:Hope someone finds it useful.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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Ger

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?

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))

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.

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.

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

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.