# Thread: Populate Cells with Row/Column Intersection

1. ## Populate Cells with Row/Column Intersection

Hello All,
Sorry if the title is a bit confusing. My Class Cost worksheet has cells to enter the range and step, the pricing is based on the combination of the Range and Step. The Pricing Structure worksheet has the pricing for each range and step intersection. If I enter Range A01 and Step B in the Class Cost worksheet I would like Excel to go to Pricing Structure worksheet and find the intersection of A01 and B and populate cost of \$748.50 in cell D2 of the Class Cost worksheet.

I have attached the workbook with the sample data.

Sorry if this is confusing...any assistance would be greatly appreciated!

2. MOSTATE,

Here's the Formula: =VLOOKUP(\$B2,PricingTable,MATCH(\$C2,PricingSteps,0 ))

Range Names Required:

PricingSteps ='Pricing Structure'!\$B\$1:\$E\$1
PricingTable ='Pricing Structure'!\$A\$2:\$E\$7

MoState Vlookuip.JPG

HTH

3. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

Maudibe (2015-07-14)

4. Thanks for the quick reply. It works great until I Autofill the other cells. I have attached the revised workbook.
Thanks!

5. Mostsate,

As an alternative, this works in D2 then copy down

=INDEX('Pricing Structure'!\$A\$2:\$E\$7,MATCH(B2,'Pricing Structure'!\$A\$2:\$A\$7,0),IF(C2="A",2,IF(C2="B",3,IF (C2="C",4,IF(C2="D",5,"")))))

You could use named ranges as well

HTH,
Maud

6. MOSTATE,

My BAD! I forgot to check the results. You need to redefine the PricingSteps range name to:
='Pricing Structure'!\$A\$1:\$E\$1

HTH

7. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

MOSTATE (2015-07-15)

8. Using Named Ranges:

In cell D2 then copy down:
=INDEX(TRng,MATCH(B2,ColRng,0),IF(C2="A",2,IF(C2=" B",3,IF(C2="C",4,IF(C2="D",5,"")))))

Where:
TRng (the table range) ='Pricing Structure'!\$A\$2:\$E\$7
ColRng (column lookup) ='Pricing Structure'!\$A\$2:\$A\$7

HTH,
Maud
Mostate.png

9. MOSTATE,

One more iteration. This version uses Dynamic Range Names...this means that you can add rows & columns to your Pricing Structure page and everything will adjust automatically. I have set Limits though of Row 1001 and Column Z adding past either will not be captured but I figured that would be sufficient and help to quicken calculations.

New Range Name Values:

PricingSteps =OFFSET('Pricing Structure'!\$A\$1,0,0,1,COUNTA('Pricing Structure'!\$A\$1:\$Z\$1))
PricingTable =OFFSET('Pricing Structure'!\$A\$2,0,0,COUNTA('Pricing Structure'!\$A\$2:\$A\$1002),COUNTA('Pricing Structure'!\$A\$1:\$Z\$1))

The formula does not change!.

Test File: Populate Cells with Column Row Intersection3.xlsx

HTH

10. RG,

Take a look at your formula when you place a non valid Range in column B (cell B4)

Mostate2.png

It is only finding an approximate match on the first 3 characters of the search values in column B. I thing you need to add the FALSE statement to your formula

=VLOOKUP(\$B2,PricingTable,MATCH(\$C2,PricingSteps,0 ),FALSE)

11. ## The Following User Says Thank You to Maudibe For This Useful Post:

RetiredGeek (2015-07-14)

12. Maud,

Right! Thanks for catching that.

13. RG,

My pleasure. I like the way you used the Match function to find the column. Much more efficient than my conditional embedded if statements.

Maud

14. Hey Y'all,

Of course you could check your input by adding this to the "Class Cost" Worksheet Module:
Code:
```Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim wksPS As Worksheet
Dim vTest As Variant

If Target = "" Then Exit Sub

Set wksPS = ActiveWorkbook.Sheets("Pricing Structure")

If Not Intersect(Target, Range("B:B")) Is Nothing Then

On Error Resume Next
vTest = Application.WorksheetFunction.Match(Target, wksPS.Range("A2:A" & _
Range("PricingTable").Rows.Count + 1), 0)
On Error GoTo 0

If vTest = "" Then
MsgBox "Value " & Target & " Entered at: " & _
Target.Address(, , xlA1) & vbCrLf & _
"is NOT in the Pricing Structure Table..." & _
vbOKOnly + vbCritical, _
"Error: Unknown Pricing Range"
Target.Value = ""
End If

End If

If Not Intersect(Target, Range("C:C")) Is Nothing Then

On Error Resume Next
vTest = Application.WorksheetFunction.Match(Target, Range("PricingSteps"), 0)
On Error GoTo 0

If vTest = "" Then
MsgBox "Value " & Target & " Entered at: " & Target.Address(, , xlA1) & vbCrLf & _
"is NOT in the Pricing Structure Table as a Step..." & vbCrLf & _
vbOKOnly + vbCritical, _
"Error: Unknown Pricing Step"
Target.Value = ""
End If

End If

End Sub  'Worksheet_Change()```
HTH

15. You are so awesome...this is just what I needed.

Thanks so much

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•