Searching 2D Excel array for a value, returning the value of the adjacent cell

Question

I've been scouring this website throughout my last 2 programming classes, and decided it was worth joining this fabulous community!

In a nutshell, I want to create an "invoice maker", which allows one to first select a category via a dropdown box in A1, then an item within that category with a dropdown in B1. Excel would then autofill the price in the next column C1.

Essentially 2 (not sure what to call it: cascading, nested, dependent) dropdown boxes that when both filled, return a price. For example:

  A        B    C
1 Category Item [Price]

Both dropdowns use Data Validation referring to a Defined Name, which is based on an array in sheet "Database". This sheet is formatted like so:

category1 price category2 price category3 price category4 price ...
item1     $xx   item1     $xx   item1     $xx   item1     $xx
item2     $xx   item2     $xx   item2     $xx   item2     $xx

I used this column arrangement mainly because it allows for each category to be expanded indefinitely. If you see a simpler way, let me know!

Essentially, to print the price in C1, my objective is to find the item name in the 2D Excel database, and return the cell immediately to the right of it. Ideally the formula in C1 would use cell A1 to determine which category to search, and search this column for the corresponding item name in B1. Then, it would +1 offset that to the right, thereby referring to the price.

As far as formulas, I have some rough pseudocode, but I'm confused between VLOOKUP, OFFSET, MATCH, and INDEX. Basically:

=INDEX( "defined name of my entire database" , MATCH( B1, **column corresponding to chosen component** , 0 ) + 1 , MATCH( A1 , components , 0 ) )

The way I see it, the INDEX would return the value of row+1 item, in a given column. The first MATCH would find the row in which the item is found, and the second MATCH would determine the column that category is found in.

Now my question is, how do I make the first MATCH search ONLY the column which corresponds to the right category?

Honestly I have no idea if my pseudocode is correct. I translated it to Formula, and it simply returns #N/A in the cell.

Many thanks for any help!!!

Answer 1

I would try the following approach:

Take the first column of the database sheet and offset it so many columns to right how the searched category is offset from the left. Then we have the price column of this category because we have offset it 1 column if it matched the first categrory, 3 if it matched the second category and so on.

From this column indicate the value from the row which is the same as the row in the found offset-1 column (the category column) which matches the searched item name.