MATCH function in a VLOOKUP

[Guest]

Hi,

I am trying to determine the column index number in a vlookup with a matcxh
function but I'm getting an #N/A error message.

The table I am looking up may change in shape (e.g. not always the same
number of rows or columns) so I have given this a name range.

The table (called NAME_RANGE) is basically

ZZ YY XX
A 10 40 70
B 20 50 80
C 30 60 90
TOTAL 60 150 240

The formula I am using is more or less:

=VLOOKUP("TOTAL",NAME_RANGE,MATCH("YY",NAME_RANGE),FALSE)

I should get 150.

What have I done wrong?

Cheers
TT

 

[Epinn]

Regarding the MATCH function, do you want match_type to be 0 or FALSE i.e. exact match? How is the table sorted?

****************************************************************************************
Following from Excel Help:-

If match_type is omitted, it is assumed to be 1.
If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order.

If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
****************************************************************************************
Have you tried to use evaluate formula to watch the steps unfold?
Interesting formula. I'll go and explore some more now while we wait for the experts' advice.

Epinn

Hi,

I am trying to determine the column index number in a vlookup with a matcxh
function but I'm getting an #N/A error message.

The table I am looking up may change in shape (e.g. not always the same
number of rows or columns) so I have given this a name range.

The table (called NAME_RANGE) is basically

ZZ YY XX
A 10 40 70
B 20 50 80
C 30 60 90
TOTAL 60 150 240

The formula I am using is more or less:

=VLOOKUP("TOTAL",NAME_RANGE,MATCH("YY",NAME_RANGE),FALSE)

I should get 150.

What have I done wrong?

Cheers
TT

 

[Biff]

Is NAME_RANGE a dynamic range?

Try this:

=VLOOKUP("TOTAL",NAME_RANGE,MATCH("YY",INDEX(NAME_RANGE,1,),0),FALSE)

Biff

 

[Epinn]

TT,

Forgive me for borrowing the space of your thread. I want to ask Biff something. Forgive me for thinking aloud too. Bad habit.

Biff,

I had a feeling that MATCH ( ) in the formula was the culprit so I pulled it out and explored it.

I was able to achieve getting #N/A quite a few times. One case is not specifying "0" or "FALSE" which I suggested in my previous post.

Is it true that the argument for MATCH ( ) can either be a *single* column or a *single* row only? We can't use an array A15 (Name_range), right? It gave me #N/A even when I tried to use CSE. I suspected MATCH and Name_range together was the problem in the formula but I didn't know how to fix it although INDEX ( ) did pop up. Next time, I'll know.

Look forward to your guidance. I really like to know about the single column/row.

Epinn

Is NAME_RANGE a dynamic range?

Try this:

=VLOOKUP("TOTAL",NAME_RANGE,MATCH("YY",INDEX(NAME_RANGE,1,),0),FALSE)

Biff

 

[Roger Govier]

Hi Trevor

I would be inclined to use Index Match for my result, rather than a mix
of Vlookup and Match
Regardless of the shape of your table, if the top left corner is always
A1 then
=INDEX(NAME_RANGE,MATCH("Total",A:A,0),MATCH("YY",(1:1),0))

If your NAME_RANGE does not always start in A1, then I would have 2
other named ranges,
Myrow which defined the row with the headings (e.g. $7:$7),
and Mycol which defined the column with the names and Total (e.g. $C:$C)
Then
=INDEX(NAME_RANGE,MATCH("Total",Mycol,0),MATCH("YY",Myrow,0))

 

[Ponty'NPop]

Hi Trevor,

Perhaps,

Name the cell TotalYY and reference that name thereafter?

HTH,
--
Kevin James.
Tua'r Goleuni


| Hi,
|
| I am trying to determine the column index number in a vlookup with a matcxh
| function but I'm getting an #N/A error message.
|
| The table I am looking up may change in shape (e.g. not always the same
| number of rows or columns) so I have given this a name range.
|
| The table (called NAME_RANGE) is basically
|
| ZZ YY XX
| A 10 40 70
| B 20 50 80
| C 30 60 90
| TOTAL 60 150 240
|
| The formula I am using is more or less:
|
| =VLOOKUP("TOTAL",NAME_RANGE,MATCH("YY",NAME_RANGE),FALSE)
|
| I should get 150.
|
| What have I done wrong?
|
| Cheers
| TT
|

 

[Guest]

=INDEX(NAME_RANGE,MATCH("TOTAL",A1:A5,0),MATCH("YY",A11,0))

 

[Roger Govier]

Hi Kevin

Tua'r Goleuni

Were you at Cardiff High School?

 

[Biff]

can either be a *single* column or a *single* row only?

We can't use an array A15 (Name_range), right?

That is correct. Match only works on one dimensional arrays.

Biff

TT,

Forgive me for borrowing the space of your thread. I want to ask Biff
something. Forgive me for thinking aloud too. Bad habit.

Biff,

I had a feeling that MATCH ( ) in the formula was the culprit so I pulled it
out and explored it.

I was able to achieve getting #N/A quite a few times. One case is not
specifying "0" or "FALSE" which I suggested in my previous post.

Is it true that the argument for MATCH ( ) can either be a *single* column
or a *single* row only? We can't use an array A15 (Name_range), right?
It gave me #N/A even when I tried to use CSE. I suspected MATCH and
Name_range together was the problem in the formula but I didn't know how to
fix it although INDEX ( ) did pop up. Next time, I'll know.

Look forward to your guidance. I really like to know about the single
column/row.

Epinn

Is NAME_RANGE a dynamic range?

Try this:

=VLOOKUP("TOTAL",NAME_RANGE,MATCH("YY",INDEX(NAME_RANGE,1,),0),FALSE)

Biff

 

[Guest]

Thanks Roger,

That worked a treat. I will need to name range the row and column headers.

Cheers.
TT

 

[Epinn]

Biff,

Thanks for answering my various questions. Appreciate it.

Epinn

can either be a *single* column or a *single* row only?
We can't use an array A15 (Name_range), right?

That is correct. Match only works on one dimensional arrays.

Biff

TT,

Forgive me for borrowing the space of your thread. I want to ask Biff
something. Forgive me for thinking aloud too. Bad habit.

Biff,

I had a feeling that MATCH ( ) in the formula was the culprit so I pulled it
out and explored it.

I was able to achieve getting #N/A quite a few times. One case is not
specifying "0" or "FALSE" which I suggested in my previous post.

Is it true that the argument for MATCH ( ) can either be a *single* column
or a *single* row only? We can't use an array A15 (Name_range), right?
It gave me #N/A even when I tried to use CSE. I suspected MATCH and
Name_range together was the problem in the formula but I didn't know how to
fix it although INDEX ( ) did pop up. Next time, I'll know.

Look forward to your guidance. I really like to know about the single
column/row.

Epinn

Is NAME_RANGE a dynamic range?

Try this:

=VLOOKUP("TOTAL",NAME_RANGE,MATCH("YY",INDEX(NAME_RANGE,1,),0),FALSE)

Biff