# Index/Match and return adjacent cell or workaround

1. ## Index/Match and return adjacent cell or workaround

Hi folks, Can anyone help me with this little cost sheet problem?

I have an index/match formula working that returns the ‘price’ after index matching ‘supplier’ and ‘term’ on another worksheet holding my rate card. I want to use the same formula or similar (or a workaround) in the adjacent cell so that the ‘per’ and ‘min hire’ fields are automatically populated as per the rate card.

Is there a way to get the index match to look up a value and then return the value in the adjacent cell? Or the adjacent cell +1? In theory it should really index the term and supplier and then return 'price', 'per' or 'min hire' depending on my formula. Looking up adjacent cells doesn't really allow me to expand the rate card over time. Rather, it's just a little clunky.

I know it is possible to adjust the range of the index to search only certain parts of the table but this doesn't solve my problem as I need the values in the price, per and min fields to update whenever I change/update terms or suppliers via the dropdowns i.e. the index must always search the data range in its entirety.

I have my cost sheet and rate card on two separate tabs. Please see attached example.

NB – Suppliers and terms are ‘Named’ in my working sheet so they appear in list form in the data validation drop downs.

Also - cell refs might be slightly out since I have culled the example from my working sheet.

Thanks,
jim

2. ## Re: Index/Match and return adjacent cell or workaround

Add +1 to the COLUMN() argument of the INDEX() formula.

e.g

=INDEX('Rate Card'!B4:J5,MATCH(A3,Suppliers,0),MATCH(B3,Terms,0)+1)

and +2 to get the next column

=INDEX('Rate Card'!B4:J5,MATCH(A3,Suppliers,0),MATCH(B3,Terms,0)+2)

3. ## Re: Index/Match and return adjacent cell or workaround

Ah yes - seems so obvious now. Thanks for your help.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1