# Higher-End Excel Formula Help

Copper Contributor

# Higher-End Excel Formula Help

Hello. Working on a massive spreadsheet. Background needed to understand question:

- You have (3) Columns, Column A, B and C

- These (3) columns are on Sheet "1"

- All three columns have the same 500+ entries

- Column A is based off a Data Validation list, and Column B is based off a different Data Validation list

- Column C is determined by the inputs of Column A and B based off an Index Match to a different table

HERE'S THE QUESTION:

I have another tab, say Sheet "2". I need a cell (Let's say D4) to fill with the value from Column C based off the inputs from Columns A and B. Here's the kicker....there may be multiple instances where Column A and B (say rows 40-80) have the same value, so rows 40-80 in Column C have the same output. For my case, that is OK & I need to return that value in Column C in rows 40-80.

I've tried a combination of things, including =IF(AND(VLOOKUP(....))) statements and can't seem to come to my answer. I can do a VLOOKUP by naming the whole 500+ entry table as an Array (say ARRAYINPUT) and have my column counter set to 3, but I can't seem to put it all together.

Any help would be appreciated. Thank you!

29 Replies

# Re: Higher-End Excel Formula Help

You will obtain help faster, if you attach your sample file.

# Re: Higher-End Excel Formula Help

If it's what I think it is, performing a lookup where there's multiple returns for the same lookup value(s), it's very do-able with INDEX. A sample of the workbook would help.

# Re: Higher-End Excel Formula Help

@Patrick2788 please see attached. I want to return the PANEL NAME based off the PANEL NUMBER and PHASE & POLE. It looks like I can't use and AND statements with multiple VLOOKUP functions in it, but I'm not sure how INDEX will work when the NUMBER and PHASE & POLE changes (need to have PANEL NAME show up on other tabs)

# Re: Higher-End Excel Formula Help

Please see attached for file. I want to return the PANEL NAME based off the PANEL NUMBER and PHASE & POLE. It looks like I can't use and AND statements with multiple VLOOKUP functions in it, but I'm not sure how INDEX will work when the NUMBER and PHASE & POLE changes (need to have PANEL NAME show up on other tabs)

# Re: Higher-End Excel Formula Help

It's possible to avoid use of AND logic and modify the VLOOKUP to handle multiple criteria in lookup and table array if needed. I'm not clear on where (Sheet and cell) you need this formula.

# Re: Higher-End Excel Formula Help

@Patrick2788 My apologies - I deleted too much out. Please see updated file w/ the blank Panel Schedule templates. What I need is the PANEL NAME (from PANEL INPUT tab) to be referenced into the space where it says PANEL NAME under the 1Ø or 3Ø PANEL SCHEDULES tab

# Re: Higher-End Excel Formula Help

Does this mean the formula should pull DP12, DP7 and DP20 OR just pull DP12 for that first example in the 30 panel schedules.

# Re: Higher-End Excel Formula Help

@Patrick2788 The latter of two. For DP12, since it is PANEL 1 for the 3Ø 42P template, that would be the name to populate PANEL NAME under the 3Ø PANEL SCHEDULES tab for the 3Ø 42P template.

Side Note: I haven't mass-copied my blank panel schedules templates horizontally to the right. So in theory, if I copied my 3Ø 42P template to the right, DP7 would be the name to fill that second template. I wanted to get the logic down first before making mass-templates.

# Re: Higher-End Excel Formula Help

@Andruw1

Why not use a partial match on the value in B2?

e.g.

=MATCH("*"&LEFT(B2,2)&"*",'PANEL INPUT'!\$B\$7:\$H\$7,0)

Feed that into the INDEX.

# Re: Higher-End Excel Formula Help

@Patrick2788 I typed that formula into B2 and I got a return value of 1, is there something else I need? I double checked that my formula was correct. I also tried editing the number value to 7 to include all of "3Ø, 42P"

And the reason why I've tried to do it this way is because Column D "LOAD DESCRIPTION" is the next piece of information I want to have referenced into my PANEL SCHEDULES templates based off the PANEL#, Ø & POLE, PANEL NAME and CIRCUIT#. If I could figure out how to get the PANEL NAME to show, it should be the same logic to populate the LOAD DESCRIPTION as well

# Re: Higher-End Excel Formula Help

I've attached my solution.   If this isn't perfect it may at least give you some ideas.

# Re: Higher-End Excel Formula Help

@Patrick2788 Thank you for providing your response. I want to make sure I understand the logic completely for the Panel Name and Load Description, as I did not think of IFERROR at all

Panel Name: Simple HLOOKUP. Looks for "3Ø, 42P", Searches my table (including bolded headers) on PANEL INPUT, searches row 2 (first row of PANEL NAMES) since it is PANEL #1 of my templates, and returns the match. I think should work for my other templates, with the 2 increasing by n+1 for each panel

Load Description: So with IFERROR, it will "catch" the error and provide an alternative solution. That solution is a VLOOKUP. In the DP12 example, the lookup is for the following: "1" for Panel #1 (locked value), B2 for Phase and Pole (locked value), D5 for Panel Name (locked value) and B11 for the circuit number (unlocked value, changes based off load description cell). For the tables to retrieve the value from,  this is where I have a question. I haven't used the CHOOSE function before, can you elaborate on it some if you wouldn't mind? I get the PANEL INPUT range values after that, as that is the table I want to choose the values from.

Thank you again for your help

# Re: Higher-End Excel Formula Help

CHOOSE allows us to concatenate the columns in the table array -- the goal is to create a unique list of items to pick from.  The other approach would be to use an array to arrive at the correct row number based on multiple criteria.  The array would then be used in an INDEX.  CHOOSE is a bit more cleaner.

# Re: Higher-End Excel Formula Help

@Patrick2788 I see. Can you explain what the {1,2} does then? That is the last part I'm not 100% clear on. After doing a little research, I see that the index_num argument is a value you choose but not sure how that ties in with this context

# Re: Higher-End Excel Formula Help

{1,2} indicates two vertical arrays. Think of how CSV delimits columns - it uses commas. The same for arrays. The {1,2} is our way of saying the values used in CHOOSE will be arrays and not single values.

# Re: Higher-End Excel Formula Help

@Patrick2788 I think I get that. If I wanted to use the same "structured formula" for the VA, why can't I switch the value_2 from D29-D500 to E29-E500? I get a blank cell when I do so

# Re: Higher-End Excel Formula Help

I surmise that I must now interpose with this question:

In your latest attached file, in which cell do you want to create your formula? Once you identify that cell, please state your expected result thereon and the logic thereof.

# Re: Higher-End Excel Formula Help

Is there something for VLOOKUP to return for that one? It could be bringing back an empty (which should usually show as 0).

# Re: Higher-End Excel Formula Help

@Patrick2788 @Twifoo See attached for latest spreadsheet.