Link to home
Start Free TrialLog in
Avatar of juschuma
juschuma

asked on

How to combine Hlookup with Vlookup to get 1 cell value.

Part 1 - How can I get the value from the cell at an intersection of a row and a column.  I want it to be based on the row heading and column heading (so it's dynamic) and not based on the actual column or row (A, B, 1, 2, etc.)

Example:

Name       Age   Sex    Marital Status  Value1   Value2
Bob         23    M       Single         5        15
Joe         42    M       Married        4        8
Jill        26    F       Divorced       7        23
Steve       19    M       Single         2        34

If on another sheet I want to get the value from Row "Steve" and Column "Marital Status" to get "Single", how do I do this:

          Marital Status
Steve     FORMULA TO RETURN "Single"

I want it to be dynamically based on both the row and column, so if I change Steve to Jill, the formula's results change to "Divorced".

Part 2 - Is there a way to do the same thing as above, but summing the last two columns (Value1 and Value2) without having to do the solution from above twice (adding the two results together).

Example
        Summed Column
Steve   FORMULA TO RETURN 36

Hope this all makes sense.

Avatar of sebastienm
sebastienm

Using named ranges to make it simpler:
-Name your first row (header names) 'Header'
-name your data range 'Data' (it can include 'Header')

then
=VLOOKUP("Steve",Data,MATCH("Sex",Header,0),FALSE)
or
A1: "Steve"
A2: header name
=VLOOKUP(A1,Data,MATCH(A2,Header,0),FALSE)

Regards,
Sébastien
juschuma,

First, change the title for "Marital Status" to what Excel would recognize as one word.  I used "Marital_Status" (without the quotes).

Highlight your data set, in your example from the word Name to the number 34, and choose, Insert, Name, Label, Place a check in the radio button for Column Labels, and click on Add, OK.

Now in your cell type in the formula:
=Steve Marital_Status
which should return: Single

For your summing formula:
=Joe Value1 + Joe Value2
which should return:12

Hope this helps.
Avatar of juschuma

ASKER

Bkpchs237,

Tried that and I get an error message, "Error in Loading DLL".

Any thoughts?
juschuma,

You didn't mention what version of Excel you have installed.  If you have Excel 2000, try Help, Detect and Repair.  Also, try reregistering Excel via Start, Run, "excel.exe /regserver" (without the quotes).  Check what Add-ins you have installed and make sure that they are in good working order.  I'd try to take each out one at a time and then add it again to make sure that they are working fine.  The method I stated is the easiest and simplest method to accomplish what you desire using words as opposed to cell references.

Hope this helps.
juschuma,

Part 1:

Enter your table on sheet2
Enter =VLOOKUP(A3,Sheet2!A3:F7,4,FALSE) in cell B1 on sheet1
Enter Steve in cell A1 on sheet1 and you see single
Enter Jill in cell A1 on sheet1 and you see married


For part 2:

Enter your table on sheet2.
Enter =VLOOKUP(A1,Sheet2!A1:F5,5,FALSE)+VLOOKUP(A1,Sheet2!A1:F5,6,FALSE)
in cell B1 on sheet1.
Enter Steve in A1 and you'll see 36 in B1
Enter Jill A1 and you'll see 30 in B1

I hope this helps
Regards
Curt
ASKER CERTIFIED SOLUTION
Avatar of arudenstein
arudenstein

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Offset and match worked well.  Thanks.