CODEWITHSUNDEEP
excelexcel-formulalookup
Sergey Karpushin
Sergey Karpushin

Reputation: 874

How to find table cell value based on condition provided in column and row?

Title might not be that descriptive, but didn't find a better one. Basically please look at the screenshot:

Excel screenshot

Table A1:I7 - is a table of Factors. This is dictionary data which allows me to select the factor based on A and B values.

Table A11:C15 - is a table where I have to put a lot of input data (pairs of A and B) and want its Factor column to be populated using the Factors table.

Is there a way to make Excel automatically calculate (or should I say - lookup) the value for the factor according to values of A and B in that row, without macro usage, just using formulas?

Upvotes: 2

Views: 11025

Answers (2)

pnuts
pnuts

Reputation: 59495

Please try =INDEX($C$4:$H$7,MATCH($B11,$B$3:$B$7,1),MATCH($A11,$B$3:$H$3,1)) in C11 and copied down, but change B3:H3 to 0, 50, 60 etc and B4:B7 to 70, 80, 90, 100. (OOps - and insert a blank row at the top!)

Edit - slight problem with cell references!:

SO19276702 example

This is a generalised solution. The nature of the factors (within limits, 1/10th change per step in every direction) may allow a simpler but more specific solution.

Also, formula should be wrapped in a condition that sets the factor to 0 when either A or B exceeds 100. Values such as 79.5 are not specifically catered for.

Upvotes: 2

rajah9
rajah9

Reputation: 12339

I'm not sure there's a way to do this in the table you have presented. However, here's a start.

Please take a look at Excel's VLOOKUP function. http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx. HLOOKUP does the similar, but is a Horizontal lookup.

You would have to do some minor tweaking to read the numbers for row 12 so that they do an HLOOKUP on row 5. (You would change the numbers in row 2 from a range to cutoff.)

Not sure how to do a VLOOKUP combined with an HLOOKUP, which seems to be what you need. I suppose you could multiply A by 100 and add in B and unroll the table into a long row.

Upvotes: 0

Related Questions