vlookup using calculated references

  • Thread starter Thread starter Robin S.
  • Start date Start date
R

Robin S.

Why can't vlookup use calculated references?

One table has its reference values listed like 123-456 (the table from
which values must be extracted).

The other table has its values listed like 123456 (the table which
dictates which values to extract).

So I used =SUBSTITUTE to remove the "-" in the first table. Vlookup
doesn't seem to be able to use the adjusted reference value in the
first table.

Is there a work around?

Regards,

Robin
 
VLOOKUP can certainly use calculated references:


=VLOOKUP(LEFT(A1,3) & "-" & RIGHT(A1,3), Table, 2, FALSE)

finds 123-456 when A1 contains 123456.

OTOH, when you converted "123-456" to "123456", the converted values
remained text, not numbers. Your A1 value (123456) is a number.

You can fix this a couple of ways:

1) convert the table values to numbers: Copy an empty cell. Select your
table's first column. Choose Edit/Paste Special and select the Add and
Values radio buttons.

2) convert the lookup value to text: One way:

=VLOOKUP(A1&"",Table, 2, FALSE)
 
Robin,

You could use an array formula like this

=VLOOKUP("123456",SUBSTITUTE(J6:L10,"-",""),2,FALSE)

As an array formula, it is entered with Ctrl-Shift-Enter.

Problem with this is that if the target value has a '-' in it, this is also
substituted.

Alternative is

=INDEX(K6:K10,MATCH("123456",SUBSTITUTE(J6:J10,"-",""),0),1)

which is also array formula, but it retains the '-' in the target.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
JE McGimpsey said:
VLOOKUP can certainly use calculated references:


=VLOOKUP(LEFT(A1,3) & "-" & RIGHT(A1,3), Table, 2, FALSE)
Click to expand...

This worked perfectly. Thanks for the help.

Regards,

Robin
 
I spoke too soon.

It didn't occur to me that the numbers are not necessarily 123-456. They can
be 123-45-6789-01 or 12-3456789 or any number of others.

I tried Bob Phillips' suggestion of

=VLOOKUP("123456",SUBSTITUTE(J6:L10,"-",""),2,FALSE)

In the case of my test spreadsheet it looked like this (plus the {}):

=VLOOKUP(A1,SUBSTITUTE(Sheet2!$A$1:$C$1,"-",""),3,FALSE)

But it turns up a value of #NA.

Doh: I just changed the A1 in mine to the "123456" in Bob's example and it
worked! Why can't I specify a reference?

Thanks again for the help.

Regards,

Robin
 
Hi Robin,

maybe your data in A1 is formated as number and not text. In this case
change the format of column A as text before entering lookup values and
it should work

HTH
Frank
 
Frank,

I used the lookup_value TEXT(A1,0) just to be sure. I don't have the actual
spreadsheet in front of my, but it worked in my test..

Thanks guys. I was ready to strangle the computer...

Regards,

Robin
 
Back
Top