greetings everybody, am hoping to confirm something. i was using VLOOKUP, to
determine a range name for the range to search, inside a VLOOKUP. kept
getting #VALUE! and this is because VLOOKUP doesn't accept a formula as the
range to do the lookup on, is that correct? or am i getting this error for
another reason?

<or am i getting this error for another reason?

How could we possibly know without seeing your formula?

But look in Excel HELP for the INDIRECT() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"mwam423" wrote in message ...
| greetings everybody, am hoping to confirm something. i was using VLOOKUP, to
| determine a range name for the range to search, inside a VLOOKUP. kept
| getting #VALUE! and this is because VLOOKUP doesn't accept a formula as the
| range to do the lookup on, is that correct? or am i getting this error for
| another reason?

hi nick, thanks for reply. please find formula below:

=VLOOKUP(Input!C39,VLOOKUP(Input!C40,Input!$C$43:$ D$55,2,FALSE),4,FALSE)

where Input!c39 equals an account number

interior VLOOKUP takes a bank name and looks up a named range which exterior
VLOOKUP needs to search

the values in Input!C39 & C40 are also lookups which change as a macro goes
through a "for loop"

so the jest of what you're saying is that you can have a formula, such as
INDIRECT, where VLOOKUP requests a range? doesn't seem to work for me, but
i'll try again . .


"Niek Otten" wrote:

<or am i getting this error for another reason?

How could we possibly know without seeing your formula?

But look in Excel HELP for the INDIRECT() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"mwam423" wrote in message ...
| greetings everybody, am hoping to confirm something. i was using VLOOKUP, to
| determine a range name for the range to search, inside a VLOOKUP. kept
| getting #VALUE! and this is because VLOOKUP doesn't accept a formula as the
| range to do the lookup on, is that correct? or am i getting this error for
| another reason?

=VLOOKUP(Input!C39,VLOOKUP(Input!C40,Input!$C$43:$ D$55,2,FALSE),4,FALSE)
interior VLOOKUP takes a bank name and looks up a named
range which exterior VLOOKUP needs to search

Try it like this:

=VLOOKUP(Input!C39,INDIRECT(VLOOKUP(Input!C40,Inpu t!$C$43:$D$55,2,0)),4,0)

However, if the named range is defined using other functions like OFFSET
this won't work.



--
Biff
Microsoft Excel MVP


"mwam423" wrote in message
...
hi nick, thanks for reply. please find formula below:

=VLOOKUP(Input!C39,VLOOKUP(Input!C40,Input!$C$43:$ D$55,2,FALSE),4,FALSE)

where Input!c39 equals an account number

interior VLOOKUP takes a bank name and looks up a named range which
exterior
VLOOKUP needs to search

the values in Input!C39 & C40 are also lookups which change as a macro
goes
through a "for loop"

so the jest of what you're saying is that you can have a formula, such as
INDIRECT, where VLOOKUP requests a range? doesn't seem to work for me,
but
i'll try again . .


"Niek Otten" wrote:

<or am i getting this error for another reason?

How could we possibly know without seeing your formula?

But look in Excel HELP for the INDIRECT() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"mwam423" wrote in message
...
| greetings everybody, am hoping to confirm something. i was using
VLOOKUP, to
| determine a range name for the range to search, inside a VLOOKUP. kept
| getting #VALUE! and this is because VLOOKUP doesn't accept a formula as
the
| range to do the lookup on, is that correct? or am i getting this error
for
| another reason?

hi biff, appreciate the response. yes, the named range is dynamic, user
fills in a list. macro goes down list and does operation for each entry on
list. the list does have a maximum number, however, so i can have named
range cover that fixed area and have macro count the number of entries and
loop through that number. thanks for your reply =D

"T. Valko" wrote:

=VLOOKUP(Input!C39,VLOOKUP(Input!C40,Input!$C$43:$ D$55,2,FALSE),4,FALSE)
interior VLOOKUP takes a bank name and looks up a named
range which exterior VLOOKUP needs to search

Try it like this:

=VLOOKUP(Input!C39,INDIRECT(VLOOKUP(Input!C40,Inpu t!$C$43:$D$55,2,0)),4,0)

However, if the named range is defined using other functions like OFFSET
this won't work.



--
Biff
Microsoft Excel MVP

I have a question regarding searching for more than one criteria to return
one value


"mwam423" wrote:

greetings everybody, am hoping to confirm something. i was using VLOOKUP, to
determine a range name for the range to search, inside a VLOOKUP. kept
getting #VALUE! and this is because VLOOKUP doesn't accept a formula as the
range to do the lookup on, is that correct? or am i getting this error for
another reason?

Hi,

Be specific about the question

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"andycocacola" wrote in message
...
I have a question regarding searching for more than one criteria to return
one value


"mwam423" wrote:

greetings everybody, am hoping to confirm something. i was using
VLOOKUP, to
determine a range name for the range to search, inside a VLOOKUP. kept
getting #VALUE! and this is because VLOOKUP doesn't accept a formula as
the
range to do the lookup on, is that correct? or am i getting this error
for
another reason?