0

This has me stumped, so I'm hoping somebody who knows the proper functions can help me out.

I am trying to do a VLOOKUP, but I want to pass the Range in based on values in columns.

The Range is on a different sheet than where the formula is, and I want the range's start column to be determined by looking for the value that is at the top of the column the forumla exists in.

For example, in the attached image, the 'Dashboard' sheet has Column A as Sheet, and the top Row has Widgets and Sprockets in the top row.

I want the Formula to be a Lookup for the search key 'Total, and return the value in the cell next to it. I want the Range to start on the sheet specified in Column A, and the Column to be the one with the value that matches the one at the top of the column where the formula is.

So my formula will look like

=VLOOKUP("Total",<INSERT RANGE HERE>,2)

Help would be appreciated.

Link to the Google Sheet:

https://docs.google.com/spreadsheets/d/1H5At3gHeTQUm6PWqeA7MT5xcm5RJ38NK6LyhSWUeaZY/edit?usp=sharing

Thanks Stack Overflow Community

Attached Screenshot

player0
  • 124,011
  • 12
  • 67
  • 124
MightyJimbob
  • 3
  • 3

2 Answers2

1

I believe this is what you're looking for.

On the Dashboard sheet enter these formulas:

C3:

=vlookup("Total", indirect($A3&"!C1:D"),2)

D3:

=vlookup("Total", indirect($A3&"!C1:F"),4)

You can then select C3:D3 and drag down to autofill. I tried using an arrayformula and it didn't work, but I might have been doing it wrong

ex

Also make sure to change "Position1" and "Position2" to match the sheet names exactly.

Edit: I just saw your screenshot. These formulas can also be put into C2 and D2, I just put them next to the sheet names to keep track of what I had to do.

Edit in response to comment: This was the closest I could get to what you're looking for.

C3:
=vlookup("Total", indirect($A3&"!C1:"&ADDRESS(ROW(INDIRECT($A3&"!$C50")),COLUMN()+1)),2)

D3:
=vlookup("Total", indirect($A3&"!E1:"&ADDRESS(ROW(INDIRECT($A3&"!$E50")),COLUMN()+1)),2)

Again, these columns can be dragged and autofilled down with no issues.

I have the following formula that can be pasted onto the dashboard C3 cell, and then copied across: =address(1,match(C$1,indirect($A3&"!1:1"),0),,,$A3) This will give me the address I want from the Sheet I am trying to reference, i.e. Position01!$C$1 Also, If I use the following formula, I get the value I want as a result: =offset(Position01!$C$1,7,1,1,1) However, If I try to combine the two, I get the error 'Argument must be a range'. =offset(address(1,match(C$1,indirect($A3&"!1:1"),0),,,$A3),7,1,1,1)

You need to add INDIRECT in front of the first formula when placing it inside of the OFFSET one so that it reads the result address as a range instead of a string:
=offset(INDIRECT(address(1,match(C$1,indirect($A3&"!1:1"),0),,,$A3)),7,1,1,1)

kaitlynmm569
  • 1,605
  • 1
  • 6
  • 18
  • Thanks for the reponse, this is almost exactly what I'm after. But rather than have C1:D,,2 or C1:F),4 what I'm after is more like... C1:(C1)+1,2), and D1:(D1)+1,2) Is there any way to evaluate the second part of the range to be just the next column over from whatever the first one was? – MightyJimbob Aug 16 '20 at 10:29
  • @MightyJimbob I have edited my answer with the best solution I could find – kaitlynmm569 Aug 17 '20 at 12:51
  • Thanks for the update. And here is where I *headdesk* and apologise. – MightyJimbob Aug 17 '20 at 22:38
  • Thanks, and this is where I headdesk and apologise... I forgot to put another 'Total' in E8. But I think what you've given me should work with a little modification. Thank you so much. – MightyJimbob Aug 17 '20 at 22:40
  • @MightyJimbob If you take the equation for D3 and change it to `=vlookup("Total", indirect($A3&"!E1:"&ADDRESS(ROW(INDIRECT($A3&"!$E50")),COLUMN()+1)),2)`, it should work. All I did was change the Total column (from C to E) and then change how many columns over it had to look to grab the total value (from +3 to +1). I have also updated my original answer. – kaitlynmm569 Aug 18 '20 at 12:03
  • If I could ask for just one more piece of help... I have the following formula that can be pasted onto the dashboard C3 cell, and then copied across: =address(1,match(C$1,indirect($A3&"!1:1"),0),,,$A3) This will give me the address I want from the Sheet I am trying to reference, i.e. Position01!$C$1 Also, If I use the following formula, I get the value I want as a result: =offset(Position01!$C$1,7,1,1,1) However, If I try to combine the two, I get the error 'Argument must be a range'. =offset(address(1,match(C$1,indirect($A3&"!1:1"),0),,,$A3),7,1,1,1) – MightyJimbob Aug 19 '20 at 00:51
  • Also, the soltuion =vlookup("Total", indirect($A3&"!E1:"&ADDRESS(ROW(INDIRECT($A3&"!$E50")),COLUMN()+1)),2) didn't quite work, as it couldn't be copied and pasted over. I think this is because the cell reference within the INDIRECT function is being treated as a string, not a cell. – MightyJimbob Aug 19 '20 at 03:16
  • To Clarify, it can be pasted down, but not across. – MightyJimbob Aug 19 '20 at 03:35
  • @MightyJimbob You need to add `INDIRECT` in front of the first formula when placing it inside of the `OFFSET` one so that it reads the result address as a range instead of a string: `=offset(INDIRECT(address(1,match(C$1,indirect($A3&"!1:1"),0),,,$A3)),7,1,1,1)`. I also added this to my answer for future reference. As for your second question, the formula with `E1` must be pasted in column `D`, with the formula with `C` pasted in column `C`. Please refer to my answer, as I clarified there. Sorry for any confusion – kaitlynmm569 Aug 19 '20 at 12:09
  • 1
    Thank you so much, this is perfect. If I need to re-do it as a VLookup I will be able to later. Much appreciated, you're a lifesaver. – MightyJimbob Aug 19 '20 at 22:45
0

use:

=INDEX({VLOOKUP("Total", Position01!C:F, {2, 4}, 0);
        VLOOKUP("Total", Position02!C:F, {2, 4}, 0)})

0

update:

if your project consists of 24 rows but unknown number of columns then you can use range:

Position01!C1:24                                   (instead of Position01!C:F)

then to return every column you can do:

COLUMN(Position01!C1:1)-1                          (instead of {2, 4})

or if you want to return every 2nd column:

FILTER(COLUMN(Position01!C1:1)-2, MOD(COLUMN(Position01!C1:1), 2)=0)  

                                                   (instead of {2, 4})
player0
  • 124,011
  • 12
  • 67
  • 124
  • Dude, this is way beyond my comprehension. Do you have a minute to explain the syntax to me? – MightyJimbob Aug 16 '20 at 10:31
  • @MightyJimbob sure, we VLOOKUP (look for) word "Total" in first column of range C:F on sheet named Position01. if the word "Total" is found in C column we return adjacent value from 2nd and 4th column of our range C:F - eg we return value from D and F column hance the {2,4}. 0 means "return only if exact match is found". then we put theses two formulas in array under each other via curly brackets {} where semicolon ; means "under each other". we are trying to fill array of cells (not a single cell) so we use ARRAYFORMULA tho that's long so we use shorter INDEX – player0 Aug 16 '20 at 10:49
  • @MightyJimbob see: https://docs.google.com/spreadsheets/d/1-NVGpYL_X5g0Zaai8nYnVjXSv3SCmZXavrr3SMx91_U/edit#gid=0 – player0 Aug 16 '20 at 10:53
  • Hi, I've been looking into this, and it's not quite what I'm after. My actual project has (currently) about 40 columns to check, and more will be added. In terms of futureproofing, this formula will require a lot of maintenance every time I insert a column (the names are actually sorted), and I am looking for a formula I can just copy and paste. This solution would have been perfect if adding columns at a later date was not necessary. – MightyJimbob Aug 17 '20 at 00:35
  • @MightyJimbob which means what exactly... looks like you don't need further help anymore with anything and your original question is totally misleading with no point after all... – player0 Aug 17 '20 at 01:14
  • I apologise if I wasn't being clear in my initial question. What I am after is a VLookup where the range is: A) On the table Specified on Column A of the Dashboard B) The start of the Range to be Specified by what is is Row 1 of the dashboard C) The End of the Range to be the Column after the start of the Range And I ideally need it to be a formula that can be copied/pasted with no further editing required. As my project will have 24 Rows, this means that every time I add a column in the middle, I will need to make 24 edits, rather than just doing a copy/paste. – MightyJimbob Aug 17 '20 at 02:01
  • I think I'm getting close to the solution I'm after using =CELL(Col, Address) – MightyJimbob Aug 17 '20 at 02:13