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

[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]

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

[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.

[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

[MrHappycapitalist]

I'm so sad that I spent 400 $ on an online course in excel - before finding this. Great stuff - I will without a doubt use this instead.

[ExcelIsFun]

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

[arnoldiusss]

Nice,

Is there a reason why you are using a non 1 based incrementer like (F$9:F9)+1 and not (F$8:F9)?

[ExcelIsFun]

@arnoldiusss , either will work!

[danielvondavis]

Thank you ¡ Great explanation

[ExcelIsFun]

@danielvondavis , you are welcome!

[hamy72]

wooooohooooooo Mike's back!..... I hope your wrist in good shape!.... Excel is fun once again!....:)

[ExcelIsFun]

@hamy72 , glad to be back!!

[missalicianeal2011]

do some excell videos with excell starter because I t dose not have slicers if it dose let me know where.

[ExcelIsFun]

@missalicianeal2011 , I do not understand what you have said.

[arnoldiusss]

@missalicianeal2011 I think your searching for PowerPivot video's?

MrExcel has some videos on that topic.