Mr Excel & excelisfun Trick 7: Reverse Lookup VBA or Formula?

[ronguiller2]

hi Mike,

sorry for the very late reply, thanks for the tip. tricks no.478 and 479 is the way to go. Love it.

[ExcelIsFun]

@ronguiller2 , cool! I am glad thatthose help!

[ronguiller2]

hi Mike,

awesome video, I never thought it was possible to do a reverse lookup for column headers and row headers, however I've noticed something. for truck no.4 you've extracted 8:00 AM on both cells instead of an 8:00 am and an 11:00 am. could it be that both data coming from the same column had somehow confused the formula?

[ExcelIsFun]

@ronguiller2 , see this video that has a more robust formula:

Excel Magic Trick 479: Reverse Two Way Lookup - Robust Formula for Duplicate Situations

or

Excel Magic Trick 478: Reverse Two Way Lookup For Date and Time Column and Row Headers

[ElmarBischof]

Awesome. I like the VBA solution.

Although... the name Reverse Index would have been closer to what the Formula does.

[WiresAnSteel]

Phew! lost me there. Great to see two giants of Xcel/VBA together in one clip.

Great job guys!

I used to be OK with basic and could dimension arrays,nest loops, call subs on variables etc but VBA is really challenging me now. Don't know yet what commands will follow each other and getting lots compile & syntax probs.

[asdee01]

I think Bills way wins!!!!!!!

[ExcelIsFun]

Cool!

[cidfidou]

Thanks Mike for your Feedback.

Great videos

[ExcelIsFun]

Did it work?

[cidfidou]

hi Mike,

Holy Cow!!! this is a mad formula that I have applied to one of my file.... It took me ages to play with ur file in order to do what I wanted... Thanks again for all your precious help.. Keep on posting great videos!!!

[ExcelIsFun]

I made two newer videos that show more robust formulas that can handle all duplicate situations:

Excel Magic Trick 479: Reverse Two Way Lookup - Robust Formula for Duplicate Situations

Excel Magic Trick 478: Reverse Two Way Lookup For Date and Time Column and Row Headers

[cidfidou]

Hi Mike

I think we have an issue with the formula if we have duplicates the same day.. Indeed if we have for example 2 Truck1 the same day, the formula will retrieve the first time of the appointment twice. Is there a way to deal with duplicates for the same day?

thanks

Once again what a great video!!!! u r the best teacher ever. :-)

[ExcelIsFun]

Yes, that is correct. I made two newer videos that show more robust formulas that can handle all duplicate situations:

Excel Magic Trick 479: Reverse Two Way Lookup - Robust Formula for Duplicate Situations

Excel Magic Trick 478: Reverse Two Way Lookup For Date and Time Column and Row Headers

[MrHelal123]

When you get tothe row/column criteria within the index and you leave it at 0 or leave it blank just like in your video, what does that do?

[ExcelIsFun]

Blank assumes it is not there.

0 for row means get whole column or give me all the rows.

0 for columnmeans get whole row or give me all the columns.

This is a hard topic for most to absorbe. Watch this video for full details on this amazing function:

Highline Excel Class 44: Basics of INDEX & MATCH functions

Highline Excel Class 45: INDEX function & MATCH function 12 Unusual Examples

[MrHelal123]

Thank you, have you got any videos on 0 for row means get whole column or give me all the rows.

0 for columnmeans get whole row or give me all the columns?

I have had a look throgh the videos you have recommended and cant seem to find this example. By the way the video shows some amazing tricks. I will study this video later on today. Awesome

[ExcelIsFun]

Try this one:

Excel Magic Trick 297: Add Certain Column Only

[kayno9]

WoooooooooooooW!! Mike.

Both methods are great. I think it was a great idea to show a VBA approach as well as the formula one. I think you guys should do more videos like this one.

Many thanks again and I look forward to next weeks duo.

[ExcelIsFun]

Dear kayno9,

I am glad that you liked it. I loved the VBA that Mr Excel did!

We'll do more!

--excelisfun

[hamy72]

Ah Mike, I'll have to watch this probably 10 more times to understand that mamoth formula.!

How on earth did you conceptualize a formula like that to begin with???

[ExcelIsFun]

Dear hamy72,

I did not conceptualize the formula. Aladin and Domenque at the Mr Excel Message Board came up with this formula. It took me many times of doing this sort of formula before I understood it. In the video you saw how I showed what each part of the formula was doing - that is the trick - but it takes a lot of practice,

And that is one of the things that makes Excel so exciting - the really hard stuff!

--excelisfun