Hello,
Please see my uploaded workbook. I begin with the PCR worksheet and paste my values in columns A through E. Column F has an array formula I got from a forum. It returns the first integer from column D. The columns G through J all contain lookup results.
In the beginning I had only one value in column A, and did not need this column. I used the integer in column F as a lookup value for VLOOKUP in the Lookup table. The Lookup table also did not have column A. I looked up column B, Mut_Start using an approximate match (please scroll to the NF1 gene, the other numbers have not been entered yet) and returned values from columns C through F back to the PCR worksheet.
Now I want to expand this to multiple genes. The gene symbols are in columns A as text strings. Each gene has a list of integers in ascending order in the Lookup table column B.
I am trying to use both the gene symbol, column A in the PCR worksheet and the Mut_Start integer, column F to find an exact match for the gene symbol and an approximate match for Mut_Start in the Lookup table. For example:
NF1 <61 should return NF1_ex1
NF1 equal or larger than 61 but smaller than 205 should return NF1_ex2
NF1 equal or larger than 205 but smaller than 289 should return NF1_ex3
and so on.
I read Chandoo's VLOOKUP book and tried an INDEX/MATCH combo as in PCR!G7 but it returns 0 instead of NF1_ex1.
What is the method of choice? I thought about a nested VLOOKUP, like first use the gene symbol to lookup a target range in the Lookup table and then the Mut_Start as a search key for an approximate VLOOKUP only in this target range. The examples in the VLOOKUP book and forum posts all seem to talk about exact lookups.
Thank you!
Please see my uploaded workbook. I begin with the PCR worksheet and paste my values in columns A through E. Column F has an array formula I got from a forum. It returns the first integer from column D. The columns G through J all contain lookup results.
In the beginning I had only one value in column A, and did not need this column. I used the integer in column F as a lookup value for VLOOKUP in the Lookup table. The Lookup table also did not have column A. I looked up column B, Mut_Start using an approximate match (please scroll to the NF1 gene, the other numbers have not been entered yet) and returned values from columns C through F back to the PCR worksheet.
Now I want to expand this to multiple genes. The gene symbols are in columns A as text strings. Each gene has a list of integers in ascending order in the Lookup table column B.
I am trying to use both the gene symbol, column A in the PCR worksheet and the Mut_Start integer, column F to find an exact match for the gene symbol and an approximate match for Mut_Start in the Lookup table. For example:
NF1 <61 should return NF1_ex1
NF1 equal or larger than 61 but smaller than 205 should return NF1_ex2
NF1 equal or larger than 205 but smaller than 289 should return NF1_ex3
and so on.
I read Chandoo's VLOOKUP book and tried an INDEX/MATCH combo as in PCR!G7 but it returns 0 instead of NF1_ex1.
What is the method of choice? I thought about a nested VLOOKUP, like first use the gene symbol to lookup a target range in the Lookup table and then the Mut_Start as a search key for an approximate VLOOKUP only in this target range. The examples in the VLOOKUP book and forum posts all seem to talk about exact lookups.
Thank you!