1
1
I am facing an issue where I need to input column indexes from 1 to 1220 in the VLOOKUP
function
{=SUM(VLOOKUP(A2,sheet1!$A$3:$AC$11, {1,2, 3, 4, 5, 6, ..., 1219, 1220}, TRUE))}
The only solution I see for that is to write a VBA function which will take a range and return an array of integers, but I need to avoid sending an Excel file with macros.
Is there any other possible way, solely based on Excel functions?
sheet example:
I need to match column A from sheet 2 with column A from sheet 1 then sum the rest of the row.
And it has to work as well if the sheet 2 is like the following:
1While there might be a perfect answer to your question, can you give a little more context on what you're trying to do? There's also likely a better way to get what you want without using an array of integers. – Dane – 2016-02-25T14:45:37.160
Since you're using
TRUE
withVLOOKUP()
, are you not expecting an exact match? – Kyle – 2016-02-25T14:59:26.427As @Dane has said, this is a classic case of an XY Problem. Let us know your actual requirement for this formula - it looks like you're simply trying to add all the values on a row where A2 roughly matches the first column...?
– Jonno – 2016-02-25T15:01:37.753@Jonno, yes that's what I am trying to achieve – Abdellah IDRISSI – 2016-02-25T15:07:36.953
@Skyline Please can you provide a sample of data, so we know what you're trying to match? Using TRUE with vlookup always seems fairly unpredictable when I've used it before. – Jonno – 2016-02-25T15:20:02.583
@Jonno I edited the question in order to provide the sample – Abdellah IDRISSI – 2016-02-25T15:36:32.420