 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I have two cells, each of which could contain 1 of 5 options.
I have a table with cell 1 options down the side, cell 2 options across the bottom and filled with every permutation.
What formula do I need to produce a result from the table for instance if cell 1 = H and cell 2 = L the table shows 200 in the corresponding row/column.
Tried VLookup but that seems to apply to lists rather than tables.
Ta!
Can you upload a screenshot? I'm confused.
Nor sure I fully understand the question but you can use HLOOKUP to get the column nested with VLOOKUP to get the cell.
Do the letters hold a numeric value then? Screenshot would help.
If I'm understanding it correctly
Cell A will contain a row reference for a table
Cell B contains a column reference for a table
He want's a formula that looks at the combination of Cell A & B and finds the corresponding result in the table?
Basically I want a formula which reads from a table of results if I tell it a value on the x axis and y axis
Edit Boardinbob has it
Would an IF scenario not work in that instance (as outlined by BoardinBob)?
think enmac has the solution
Too many variables for IF
You need a match in your lookup
=VLOOKUP(A9, $A$1:$F$6, MATCH(B9, $A$1:$F$1, 0), FALSE)
for example
In my example above
the table is A1:F6 including headings to be looked up
A9 the value to be looked up from column A
B9 the value to be matched to row 1
=VLOOKUP(B8,A1:F6,C8+1,FALSE)
Where:
cell b8 is your "cell 1"
cell c8 is your "cell 2"
A1:F6 is your table with 5x5 options and the headings for each column and row which relate to the cell 1 and cell 2 choices.
Much easier just to write a custom function and use that
Cheers RB that's smashed it! I was nearly there with INDEX-MATCH too but yours is easier
Tried VLookup but that seems to apply to lists rather than tables.
VLOOKUP is for tables but the third value defines the column in the table. You need the MATCH in the example I gave to define which column you want using your second value
Mixing VLOOKUP & INDEX/MATCH makes me uncomfortable 🙂
Assuming the labels are in column A & row 6, and data in range B1:F5:
=INDEX(B1:F5,MATCH(cell1,A1:A5,0),MATCH(cell2,B6:F6,0))