Excel Magic Trick 1254: Lookup Item Where 2 Values Are Closest: Helper Column or Array Formula?

Learn how to create solution to lookup an item where two values are closets in value:
1. (00:10) Problem Introduction
2. (00:36) Helper Column Solution and two cell formulas using ABS, MIN and INDEX & MATCH functions.
3. (03:15) Single Cell Formula using ABS, MIN and INDEX & MATCH functions, and two array operations. Learn about what an array operation and array formula is, and learn how to enter an array formula into a cell using Ctrl + Shift + Enter.
4. (08:13) Look at list of functions that can handle array operations without any special keystrokes.

Alan Douglas at YouTube asks:
I have two columns running side by side with random numbers.
And I would like a formula that will find the which two values (one in each column) are the closest in value...

[pmsocho]

Array formulas are awesome! Every Excel power user who wants to learn CSE formulas should buy and read your Ctrl+Shift+Enter book!

[ghazwanzinah]

Thank you Mike we learn from you day by day

[HusseinKorish]

The way you explain the formulas makes it perfect ... many thanks

[lazalazarevic]

thank u for another great formula and a few ideas
love your lessons

[LotfyKozman]

By using PQ we can:
1. create this helper column
2. remove the other two main columns
3. Merge the Item col with the helper col together and separate them with a separator
4. sort from A-z
5. Close and loat data to get the updated result each time.
Hope this helps

[alandouglas]

Awesome work Mike, the way you explain it is perfect.
Thanks for taking the time to make and upload a video regarding my question. It works perfectly.

Please keep these videos going, It always amazes at what excel can do

[mdyaseen]

Its a pleasure to learn from you and thank you so much for making educated to us and i have one problem hope and expect to solve from you, how can i get suppliers details when i select sub item from the user form for example if i want to purchase a computer, VBA to show only computer suppliers and details of the supplier

[stephanweaver]

I did not need to @lookup the answer, but your teaching method I could not @match

[Sal_A]

As a substitute for Index/Match, you can also use Vlookup with Choose to lookup left. Space operator lookup should also work.

[NormanVirciglio]

does the newer version of excel allow use of an array formula in a merged cell? I know of three work arounds.1) using a helper cell. 2) unmerging, then write the array formula, then re-merging. 3) un merge and then use a center across selection format.. Any insights are welcome..

[watamote]

Help me for this
Assume we have 8 x 8 Tabe, Table have Header Row & Column ( Header Row (1x8), Header column (8x 1 )) in 8x8 Table/ 64 cell i will peak value let consider "RxCy" Now i want relative position of cell with respect to Header Row and Column And Return value of Header Row and Column at intersections position with "RxCy". If RxCy have it duplicate Then Position of Duplicate & intersection value too

[SoftSynthPortal]

Hey! It's Nice, But what about if the Min Difference is exactly the same for two rows? (I think the first value will be the result as match gives that...)

[imranhussain]

Hi Mike. I have a TABLE in an excel sheet and want to use COUNTIF function with absolute reference. Can you please help me for below question.

COUNTIF (A$1:A1, A1)

How to write the same function in a Table format.