CODEWITHSUNDEEP
excelindexingexcel-formulamatch
Bob
Bob

Reputation: 31

Excel formula to look up a value using Row and Column values in a table

This is a two part question. I want to lookup a price of an item based upon the effective date of the price. I've seen the vertical effective date examples but mine is somewhat different. I have the Items in the first column (A). The remaining columns contain a header with the effective date of the price. Hopefully I'm able to attach the format example. The reason I have the table formatted this way is that I don't want to duplicate the items when a new price is entered. So, I want to provide the formula an ITEM and then select the current price base upon the effective date. Right now the table has 2 effective date columns. So,The second question is how can I format the table so when I add a NEW price with a new effective date (an additional column) without having to change the look-up formula? Can the formula be formatted to be dynamic?

Thanks in advance for your input.

Table Example

Upvotes: 1

Views: 279

Answers (4)

Bob
Bob

Reputation: 31

Thank you everyone for your input. It was invaluable for working a solution to my problem.

Upvotes: 0

bosco_yip
bosco_yip

Reputation: 3802

Please refers to JvdV's table, another shorter formula option

In B11, enter formula :

=LOOKUP(9.9E+307,INDEX(1:8,MATCH(A11,A:A,0),0))

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152660

You can use:

=INDEX(1:1040000,MATCH("ITEM1",A:A,0),MATCH(1E+99,1:1))

If the date is always equal to or less than today I would use:

=INDEX(1:1040000,MATCH("ITEM1",A:A,0),MATCH(TODAY(),1:1))

Where the item can be replaced with a cell reference that contains the item to be looked up.

Upvotes: 1

JvdV
JvdV

Reputation: 76000

As per my comment:

enter image description here

The formula in B11:

=INDEX(1:8,MATCH(A11,A:A,0),MATCH(TRUE,INDEX(ISBLANK(1:1),),0)-1)

As you notice I have populated A1 to get this working correctly.

Upvotes: 1

Related Questions