Using two cell values to find a result in a table

Hello. I'm struggling to get my head around a complex task in Excel and think I might be overcomplicating matters! Any help to stop me wasting time would be much appreciated.

I have a large database of customer information in worksheet 1.
Column AD = BrandContract
Column N = Throughput

I have a matrix in worksheet 2 that shows how much we should invoice a customer depending on their throughput and BrandContract
It has the BrandContract in column A, and then various throughput range targets in columns B-F (for example 0-99, 10-299, 300-499 etc.).

So, I now need to get a column that looks up the value in column AD to establish which row of worksheet 2 to look at...and then sees which of the value ranges in columns B-F it falls in to. It would return the header/title for the corresponding column.

I'm fine with standard VLOOKUP functions but really want to try and learn/understand how I add in this extra variable.

Any help would be very much appreciated.

Thank you

Originally Posted by curdley

Okay....hopefully you can see an attachment now.... thanks again for helping

Can you upload a spreadsheet with details of what you want and some expected outcomes? I cannot work out what's what in the one you've just posted!

How do I know from that number which row to look in? Or should there be another column visible?

Perhaps this will work for you. The ranges given on Sheet2 had to be converted to just the upper limits.

Formula:

Please Login or Register  to view this content.

This works better. Insert a column of zeros like this:

	A	B	C	D	E	F	G
2			Tier1	Tier2	Tier3	Tier4	Tier5
3	A1	0	£99	£199	£299	£399	£5,000
4	A2	0	£49	£99	£199	£299	£500
5	A3	0	£99	£199	£299	£399	£1,000
6	A4	0	£99	£199	£299	£399	£1,000
7	A5	0	£99	£199	£299	£399	£1,000
8	B1	0	£299	£599	£999	£1,999	£10,000
9	B2	0	£99	£199	£299	£399	£1,000
10	B3	0	£99	£199	£299	£399	£1,000

Then, enter this in Sheet1!AE2 and fill down

Formula:

Please Login or Register  to view this content.

A bit of an explanation:
Start in the middle and work outwards
INDEX(Sheet2!$B$3:$G$10,MATCH(AD2,Sheet2!$A$3:$A$10,0),0) This INDEX Sheet2!$A$3:$G$10 contains
all the rows and columns of data. So, a ROW and COLUMN has to be determined. This part of the formula returns the row number as in a "normal" formula.

When surrounded by MATCH(N2,......,1) This is in the COLUMN element of the first INDEX.
Therefore MATCH(N2,INDEX(Sheet2!$B$3:$G$10,MATCH(AD2,Sheet2!$A$3:$A$10,0),0),1) returns the column number of the value of N2.

The formula can have the ,, reduced to just a comma.

Formula:

Please Login or Register  to view this content.

Thank you for the feedback.