Where Have I Went Wrong? (Excel)

Cotta

Hi All,


I'm trying to use a double vertical lookup and a "Match" statement, however I am returned an error. How can this code be corrected? It currently advises that "too many arguments have been entered".


=VLOOKUP(VLOOKUP(A32,'EXT60'!A3:BG148,MATCH(E29,'EXT60'!1:1,0),Location!$B$3:$C$25,2,FALSE))

John_Gray

Where have you gone wrong?

Very difficult to say, since you don't say what error Excel gave you.
And there are no clues as to what data and worksheets you have.

Standard practice would be to split up the complex statement into shorter ones, in several cells, and see what error(s) you get then.

Cotta

Sorry it advises that too many arguments have been entered.

bod1467

Each VLOOKUP needs

Search term
Source array
Cell to return
True/False (for a close or exact match)

The outer VLOOKUP seems to be missing the last parameter.

Cotta

bod1467 wrote: »

Each VLOOKUP needs

Search term
Source array
Cell to return
True/False (for a close or exact match)

The outer VLOOKUP seems to be missing the last parameter.

There is a "false" at the end of the statement.


=VLOOKUP(VLOOKUP(A32,'EXT60'!A3:BG148,MATCH(E29,'E XT60'!1:1,0),Location!$B$3:$C$25,2,FALSE))

bod1467

Actually it's not just the FALSE; it's the position of the parentheses. To recap:

VLOOKUP(Search,Array,Column,FALSE)

In your case you need:

VLOOKUP(VLOOKUP(Search, Array, Column, FALSE), Array, Column, FALSE)

The nested VLOOKUP is the Search term for the outer VLOOKUP.

In your OP, the MATCH is the Column term for the nested VLOOKUP, and there is no FALSE term for the outer VLOOKUP. So ...

VLOOKUP(VLOOKUP(Search, Array, MATCH(), FALSE), Array, Column, FALSE)

bod1467

And if that wasn't what you intended then you'll need to reassess your requirements.

Cotta

Using your logic Bod I think it's almost cracked. I'm getting a "#REF!" error now and it seems to involve the value before "location". I wonder if the problem is that this calculation is taking place in cell E30 and the first part of the formula is returning a certain value, then I need the formula to use this value to carry out the second part. Is this the problem and how do I get around it?




=VLOOKUP(VLOOKUP(A31,'EXT60'!A:JI,MATCH(Template!$E$29,'EXT60'!$1:$1,0)),Location!B4:C26,2,FALSE)

Heedtheadvice

Oh do use named ranges, it makes formulae so much easier to read, especially to others!!
Plus the idea (already mentioned in another post) of breaking it down into simple steps will assist in putting the whole together.

That can best be done by having each simple formula step on a cell referenced by the next formula in the chain. It then becomes more obvious both if it works correctly (you can check the answer returned in each cell step) and, if it causes an error , see which formula causes that error.

Far too easy to make it over complicated and not see the wood for the trees and in many cases no need to combine together in a complex looking formula!

rmg1

You could also use the "Evaluate Formula" option in Excel top find which section gives you an error. There's nothing wrong with your formula from a syntax point of view (the one in post 8 anyway)

bod1467

Cotta wrote: »

=VLOOKUP(VLOOKUP(A31,'EXT60'!A:JI,MATCH(Template!$E$29,'EXT60'!$1:$1,0)),Location!B4:C26,2,FALSE)

Does Location not need ' ' around it?

'Location'!B4:C26