VLOOKUP question

  • Thread starter Thread starter mwam423
  • Start date Start date
M

mwam423

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

| 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 . .
 
=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
Click to expand...

Try it like this:

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

However, if the named range is defined using other functions like OFFSET
this won't work.
 
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
 
I have a question regarding searching for more than one criteria to return
one value
 
Back
Top