Excel 2010 Magic Trick 800: Alter VLOOKUP Table To Simplify Formula To Return Multiple Items

Download file: https://people.highline.edu/mgirvin/ExcelIsFun.htm
Rotate Table:
1. See how to add blank formulas and a row with a zero lookup to the lookup table to help simplify the VLOOKUP function formula
2. See COLUMNS function to increment numbers in a formula to generate column index numbers for VLOOKUP function.

Category:

Science & Technology

Tags:

• Excel
• 2007
• 2010
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College
• Slaying
• Dragons
• See
• how
• to
• add
• blank
• formulas
• and
• row
• with
• zero
• lookup
• the
• table
• help
• simplify
• VLOOKUP
• function
• formula
• COLUMNS
• increment
• numbers
• in
• generate
• column
• index

[thedr9wningman]

@ExcelIsFun Sadly, I have. The thread id is t=575731 I've had no responses at all. Have I made an ill-formed question? Thanks for your help, though!

[ExcelIsFun]

@thedr9wningman , maybe if you try to reply to your post t=575731 and show an example of the data set. At least that will bump the message back up to the top. But if you "show" your data and an example of the expected result, more people ususally answer...

[thedr9wningman]

How would you do this if you were using data formatted in tables? I'm having a very difficult time trying to anchor when I'm using table references. This doesn't work: vlookup(site_inspections[$Freq­uency]....). You can do it very easily using r1c1 references, but the table references confound me. I've looked everywhere and have found nothing on this.

[ExcelIsFun]

@thedr9wningman , I got it to work with a table, but I used the range instead:

=VLOOKUP($B10,$B$3:$F$6,COLUMN­S($C10:C10)+1,0)

or use something like this:

=IF(VLOOKUP($B10,Table2,COLUMN­S($C10:C10)+1,0)=0,"",VLOOKUP(­$B10,Table2,COLUMNS($C10:C10)+­1,0))

[thedr9wningman]

@ExcelIsFun I understand it works with R1C1 references. My question, though, was how does it work with structured references! Is there any way to _not_ use the R1C1? Because that is difficult once you've formatted things in tables; Excel uses [@column_name] often, and it is very difficult to get it to stay put! It is also less readable, in my opinion. I would much prefer to see a table_name[$column_name]-style function. Thanks for any assistance; this has me frustrated.

[ExcelIsFun]

@thedr9wningman , I don't know how to do that - try posting to:

mrexcel [dot] com/forum

[thedr9wningman]

@ExcelIsFun Thank you! I will try to do that.

[thedr9wningman]

@ExcelIsFun I understand it works with R1C1 references. My question, though, was how does it work with structured references! Is there any way to _not_ use the R1C1? Because that is difficult once you've formatted things in tables; Excel uses [@column_name] often, and it is very difficult to get it to stay put! It is also less readable, in my opinion. I would much prefer to see a table_name[$column_name]-style function. Thanks for any assistance; this has me frustrated.

[ExcelIsFun]

@MrHappycapitalist , I am happy that the videos help!!