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.
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.
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.
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.
ASKER
Bkpchs237,
Tried that and I get an error message, "Error in Loading DLL".
Any thoughts?
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.
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)+VL OOKUP(A1,S heet2!A1:F 5,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
Part 1:
Enter your table on sheet2
Enter =VLOOKUP(A3,Sheet2!A3:F7,4
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Offset and match worked well. Thanks.
-Name your first row (header names) 'Header'
-name your data range 'Data' (it can include 'Header')
then
=VLOOKUP("Steve",Data,MATC
or
A1: "Steve"
A2: header name
=VLOOKUP(A1,Data,MATCH(A2,
Regards,
Sébastien