Viewing 15 posts - 1 through 15 (of 15 total)
  • Excel – what formula do I need?
  • 40mpg
    Full Member

    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!

    CaptJon
    Free Member

    Can you upload a screenshot? I’m confused.

    enmac
    Free Member

    Nor sure I fully understand the question but you can use HLOOKUP to get the column nested with VLOOKUP to get the cell.

    Fresh Goods Friday 696: The Middling Edition

    Fresh Goods Friday 696: The Middlin...
    Latest Singletrack Videos
    funkmasterp
    Full Member

    Do the letters hold a numeric value then? Screenshot would help.

    BoardinBob
    Full Member

    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?

    40mpg
    Full Member

    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

    funkmasterp
    Full Member

    Would an IF scenario not work in that instance (as outlined by BoardinBob)?

    mrjmt
    Free Member

    think enmac has the solution

    40mpg
    Full Member

    Too many variables for IF

    Rubber_Buccaneer
    Full Member

    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

    chief1409
    Free Member

    =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 5×5 options and the headings for each column and row which relate to the cell 1 and cell 2 choices.

    gypsumfantastic
    Free Member

    Much easier just to write a custom function and use that

    40mpg
    Full Member

    Cheers RB that’s smashed it! I was nearly there with INDEX-MATCH too but yours is easier

    Rubber_Buccaneer
    Full Member

    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

    Sundayjumper
    Full Member

    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))

Viewing 15 posts - 1 through 15 (of 15 total)

The topic ‘Excel – what formula do I need?’ is closed to new replies.