Vlookup combined with Hlookup

[Cornelius]

I am trying to lookup data that is in a named range across columns and rows:

Rough idea as follows : Week1 Week 2 Week 3
ABC 20 30 40
DEF 10 20 30

I would like to be able to look for ABC in week2 , no matter in what column
number on the spreadsheet it may appear. The column will allways have the
heading Week2. Is there a way to combine the Vlookup and or Hlookup functions
so that I can find ABC Week2 as long as it appears in the named range but
without having to specify what column number it will be in ?

 

[T. Valko]

Try this:

...........A..........B.............C..............D.....
1................Week1.....Week2.....Week3
2....ABC.......20............30.............40
3....DEF........10............20............30

I would like to be able to look for ABC in week2

A10 = ABC
A11 = Week2

=VLOOKUP(A10,A13,MATCH(A11,A11,0),0)

 

[Cornelius]

This did not work. My data range is as you show below with ABC being in A2
and Week2 being in C1. I then have an ABC on another tab ( cell A12) against
which I want to do the formula.Above and to the right of A12 in this other
tab I also have Week2 (B11) So I called the data range "look" via insert name
define .....

My formula reads as follows

=VLOOKUP(A12,look,MATCH(B11,look,0),0)

Am I doing something wrong ?

 

[T. Valko]

If your named range includes the column headers try this:

=VLOOKUP(A12,look,MATCH(B11,INDEX(look,1,),0),0)

 

[Cornelius]

Mr/Mrs Valko .............PURE GENIUS !!!!!!!!!!!!!!!!!!!!!!!!!!

THANK YOU for the quick and kind help!

I"M SAVED !!!!!!!

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Prashath Shettigar]

Hall Sir,

With ref to below excel function Vlookup combined with Hlookup or get the value from table .......plz tell me how get the cell value from the very next matched value.

Generally using the aboce function we get the exact match from below example
if i say "ABC" & "Week2" it shows the value of 30, But along with this in the very next cell (where i specified & get this value) i need the value of week3 also (i.e., in the next cell i want the value 40 )...how to get this one.

...........................................
I seen this solution in your site for below qns

Vlookup combined with Hlookup
T. Valko posted on Wednesday, July 09, 2008 1:48 AM


Try this:

...........A..........B.............C..............D.....
1................Week1.....Week2.....Week3
2....ABC.......20............30.............40
3....DEF........10............20............30







T. Valko wrote:

Re: Vlookup combined with Hlookup
09-Jul-08

Try this

...........A..........B.............C..............D....
1................Week1.....Week2.....Week
2....ABC.......20............30.............4
3....DEF........10............20............3

A10 = AB
A11 = Week

=VLOOKUP(A10,A13,MATCH(A11,A11,0),0

--
Bif
Microsoft Excel MV


Previous Posts In This Thread:

Vlookup combined with Hlookup
I am trying to lookup data that is in a named range across columns and rows

Rough idea as follows : Week1 Week 2 Week
ABC 20 30 4
DEF 10 20 3

I would like to be able to look for ABC in week2 , no matter in what column
number on the spreadsheet it may appear. The column will allways have the
heading Week2. Is there a way to combine the Vlookup and or Hlookup functions
so that I can find ABC Week2 as long as it appears in the named range but
without having to specify what column number it will be in ?

Re: Vlookup combined with Hlookup
Try this

...........A..........B.............C..............D....
1................Week1.....Week2.....Week
2....ABC.......20............30.............4
3....DEF........10............20............3

A10 = AB
A11 = Week

=VLOOKUP(A10,A13,MATCH(A11,A11,0),0

--
Bif
Microsoft Excel MV


This did not work.
This did not work. My data range is as you show below with ABC being in A2
and Week2 being in C1. I then have an ABC on another tab ( cell A12) against
which I want to do the formula.Above and to the right of A12 in this other
tab I also have Week2 (B11) So I called the data range "look" via insert name
define ....

My formula reads as follows

=VLOOKUP(A12,look,MATCH(B11,look,0),0

Am I doing something wrong

:

If your named range includes the column headers try
If your named range includes the column headers try this

=VLOOKUP(A12,look,MATCH(B11,INDEX(look,1,),0),0

-
Bif
Microsoft Excel MVP

Mr/Mrs Valko .............PURE GENIUS !!!!!!!!!!!!!!!!!!!!!!!!!!
Mr/Mrs Valko .............PURE GENIUS !!!!!!!!!!!!!!!!!!!!!!!!!

THANK YOU for the quick and kind help

I"M SAVED !!!!!!

:

You're welcome. Thanks for the feedback!
You're welcome. Thanks for the feedback

-
Bif
Microsoft Excel MVP


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorial...f-32b2d802ae17/wpf-datagrid-custom-pagin.aspx

 

[T. Valko]

If you want 2 results then enter the formula in a cell, let's say you enter
it in B10, then copy across to C10.

=VLOOKUP($A10,$A$1:$D$3,MATCH($A11,$A$1:$D$1,0)+COULUMNS($B10:B10)-1,0)