MATCH function in a VLOOKUP

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Is NAME_RANGE a dynamic range?

Try this:

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

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 A1:D5 (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
 
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))
 
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
|
 
can either be a *single* column or a *single* row only?
We can't use an array A1:D5 (Name_range), right?
Click to expand...

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 A1:D5 (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
 
Thanks Roger,

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

Cheers.
TT
 
Biff,

Thanks for answering my various questions. Appreciate it.

Epinn

Biff said:
can either be a *single* column or a *single* row only?
We can't use an array A1:D5 (Name_range), right?
Click to expand...

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 A1:D5 (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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Match & Vlookup Function 1
Match and Vlookup issue 2
VLookup vs. Match/Index 6
Formula Using Vlookup & Match 3
Can't Explain N/A in Vlookup 2
Odd VLOOKUP result. 16
vlookup or index/match formula?? 2
Vlookup returning NA when referencing cell data to another sheet 2

Back
Top