VLOOKUP used with INDIRECT gives #REF1 error, but only with some of my worksheets

[b124816a]

Hello,

Please see the attached file "Confirmation_List_7_test.xlsm". A simple VLOOKUP was used in the beginning to use the integer in the worksheet PCR, column F as a lookup value to find in the worksheet NF1_Lookup, column A the value that is either equal or the one smaller (approximate match) and return the value from column C.

Later on this was expanded to have multiple gene symbols in PCR!A. Each value in column needed to be combined with the one in column F in order to use the proper lookup table for the value in column A.

I made a separate worksheet for each gene, called "NF1_Lookup", "NF2_Lookup" etc. and used the formula (example in PCR!G4):

=B4&"-"&VLOOKUP($F4,INDIRECT($A4&"_Lookup"),3,TRUE)

This worked, but lately I added more lookup tables, like "SOS2_Lookup" and there I am getting a #REF! error. The new worksheets are copies of previous ones, just with other values.

I checked for extra "space" characters both in PCR! column A and the names of the new worksheets and did not find any. What is different?

There may be another way of doing this. My workbook has a sizable number of worksheets and there may be a way to consolidate them in one worksheet.

I need to keep the lookup within a range of integers. For example for the NF1 gene if my lookup value is <= 60 it needs to find "NF1_ex1". If it is between 61 and 204 it needs to find "NF1_ex2" and so forth.

FYI: the array formulas in PCR! column F are extracting the leftmost integer from column D. I found it on a forum and don't understand it.

Thank you!

 

[bosco_yip]

Hi,

Check as per attached

Regards

 

[b124816a]

Thank you very much, bosco_yip! I like how you cleaned up my formulas! Sorry, I did not specify that the leftmost integer in column D, as in "c.12345" can have 1 through 5 digits (have not seen 6 or more). Is it possible to modify your formula in column F to accommodate that?

Thank you very much!

 

[bosco_yip]

........... I did not specify that the leftmost integer in column D, as in "c.12345" can have 1 through 5 digits (have not seen 6 or more). Is it possible to modify your formula in column F to accommodate that........

To extract 1-5 digits from the leftmost integer column D data,

In F4, modified formula copy down :

=IF(D4="","",LOOKUP(9^9,0+MID(D4,3,{1,2,3,4,5})))

Regards

 

[b124816a]

Great, thanks! Does the "LOOKUP(9^9" part mean lookup any numeric character?

All my lookups are working except when I have "ACADM" in column A. This one has always given a #REF! error.

I really appreciate your help and am glad to give a "kudo" (praise)!

 

[b124816a]

Great, thanks! Does the
"LOOKUP(9^9" part mean lookup any numeric character?

All my lookups are working except when I have "ACADM" in column A. This one has always given a #REF! error.

I really appreciate your help and am glad to give a "kudo" (praise)!

__________________________________________________________________
Please see the attached file Confirmation_List_8.xlsm. In the sheet PCR! the formula in cell H26 and I26, J26 and K26 gives a #REF! error only when the value of cell A26 is ACADM. The formula evaluation indicates it can not find the sheet "ACADM_Lookup". I checked for extra spaces and replaced the sheet "ACADM_Lookup" with a copy of a working one and typed in the proper values in case there was something weird with the original sheet.

What is causing the #REF! error?

Thank you!

 

[bosco_yip]

[quote="b124816a, post: 203676, member: 30164
Please see the attached file Confirmation_List_8.xlsm. In the sheet PCR! the formula in cell H26 and I26, J26 and K26 gives a #REF! error only when the value of cell A26 is ACADM. The formula evaluation indicates it can not find the sheet "ACADM_Lookup". I checked for extra spaces and replaced the sheet "ACADM_Lookup" with a copy of a working one and typed in the proper values in case there was something weird with the original sheet.

What is causing the #REF! error?

Thank you!

Check with your wording in Name Manager of "ACADM_Lookp",

Does it match with your H26 formula ? =IF(G26="","",B26&"-"&VLOOKUP($G26,INDIRECT($A26&"_Lookup"),3,1))

Regards
Bosco