hlookup & ?

[Intermediate Excel User....]

I am trying to find a way for excel to do a vlookup (or something like it)
coupled with an hlookup (to obtain a result).

Here's an example table:
Results
Group 1 0.0% 72.0% 74.0% 76.0% 78.0% 80.0% 82.0%
0.0 8.0 10.0 12.0 14.0 16.0 18.0
Group 2 0.0% 74.0% 76.0% 78.0% 80.0% 82.0% 84.0%
0.0 8.0 10.0 12.0 14.0 16.0 18.0
Group 3 0.0% 74.0% 76.0% 78.0% 80.0% 82.0% 84.0%
0.0 8.0 10.0 12.0 14.0 16.0 18.0

In a separate tab, I have the group name (eg. Group 3) and a result value
(eg. 78%). I'm trying to create/find a formula that will read the Group
name, then read the result, go over to this table (which is on a separate
tab) and have it locate Group 3, and do an hlookup for the result of 78 and
return me the value of 12. the above table is not a PivotTable.

I originally thought I could use a vlookup coupled with an hlookup but I
can't get that to work. Any suggestions?

 

[Richard Buttrey]

With your table below in A1:H6 put

=HLOOKUP(B5,INDIRECT(LEFT($A5,5)&RIGHT($A5,1)),2)
in B6 and copy across.

If you can change the name to be 'Group3' rather than 'Group 3' then
you could simplify that formula to


=HLOOKUP(B5,INDIRECT($A5),2)

HTH

I am trying to find a way for excel to do a vlookup (or something like it)
coupled with an hlookup (to obtain a result).

Here's an example table:
Results
Group 1 0.0% 72.0% 74.0% 76.0% 78.0% 80.0% 82.0%
0.0 8.0 10.0 12.0 14.0 16.0 18.0
Group 2 0.0% 74.0% 76.0% 78.0% 80.0% 82.0% 84.0%
0.0 8.0 10.0 12.0 14.0 16.0 18.0
Group 3 0.0% 74.0% 76.0% 78.0% 80.0% 82.0% 84.0%
0.0 8.0 10.0 12.0 14.0 16.0 18.0

In a separate tab, I have the group name (eg. Group 3) and a result value
(eg. 78%). I'm trying to create/find a formula that will read the Group
name, then read the result, go over to this table (which is on a separate
tab) and have it locate Group 3, and do an hlookup for the result of 78 and
return me the value of 12. the above table is not a PivotTable.

I originally thought I could use a vlookup coupled with an hlookup but I
can't get that to work. Any suggestions?

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[shail]

Hi,
If I have understood it correctly

=HLOOKUP(VLOOKUP(A10,A10:H10,5,FALSE),B10:H11,2,FALSE)

This will give you the output 12


Hope this will help you.

Thanks,

Shail

 

[Max]

One way ..

Assuming the reference table is in sheet: X,
within cols A to Z, data from row1 down

In another sheet,

In A1: Group 3
In B1: 78%

Then put in C1, array-enter the formula, ie press CTRL+SHIFT+ENTER, instead
of just pressing ENTER:
=OFFSET(X!$B$1:$Z$1,MATCH(A1,X!$A:$A,0),MATCH(B1,OFFSET(X!$B$1:$Z$1,MATCH(A1,X!$A:$A,0)-1,),0)-1)
will return the required result from X

Correctly array-entered, the formula will appear within curly braces
inserted by Excel in the formula bar: {=OFFSET(X!$B$1:$Z$1,...)}

As-is, C1 can then be copied down to return correspondingly for other pairs
of inputs in A2:B2, A3:B3, etc

 

[Max]

Here's a quick sample to illustrate:
http://cjoint.com/?jbnUWQ0Whr
DoubleOFFSET.xls
[ Link is good for 14 days ]