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

juschuma
juschuma used Ask the Experts™
on
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.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.

Author

Commented:
Bkpchs237,

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

Any thoughts?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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
I have had a lot of experience doing this sort of work.  What I think is best thing to do is use the offset and match functions.

So if name a range "Start" as an anchor in the upper left hand corner of your matrix, you could type something like this in the cell you want to return the answer:


=offset(Start,match("Steve",$B$2:$B$5,0),match("Marital Status",$B$1:$F$1,0))

this formula will have to be adjusted based on your table and where you want to the input/output cells to be, if you are more specific I can adjust the formula.

good luck
andy

Author

Commented:
Offset and match worked well.  Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial