Скачать или смотреть 2D Lookup In Excel With Crosshair Intersect Highlight - Excel Tips and Tricks

Learn how to create 2D lookup in Excel with crosshair intersect highlight.

To highlight intersections in Excel, navigate to the "Home" tab, select the range of cells you want to format, and click on "Conditional Formatting." Then, choose "New Rule" and select "Use a formula to determine which cells to format." Here, input a formula that identifies the intersection you wish to highlight, such as checking if two cell values are equal. For highlighting crosshairs, there isn't a direct feature, but you can simulate it by adding diagonal lines to cells. To conduct a two-way lookup, locate the value where a specific row and column intersect. You can achieve this by combining functions like INDEX and MATCH, which allow you to search for values in rows and columns and return the corresponding value from the intersecting cell without the need for complex formulas.

At the end of this video, you should be able to answer the following questions.
How do you highlight intersection in Excel?
How to highlight crosshairs in Excel?
How do I do a 2 way lookup in Excel?
How do you highlight intersection in Excel?
How to highlight crosshairs in Excel?
What is a 2 D lookup in Excel?
How do you lookup in a 2 dimensional array in Excel?
What is a 2D Xlookup?

Here are the steps outlined in my video.

Prep Cells For Column
1) Select cell H1
2) =MATCH(D3,D5:J5,0)+3

Prep Cells For Row
1) Select cell H2
2) =MATCH(B3,C6:C36,0)+5

Highlight Intersecting Cell
1) Select your dataset (=$B$5:$J$36)
2) Home -- Style -- Conditional Formatting
3) New Rule
4) Select "Use a formula to determine which cells to format".
5) =AND(ROW()=$H$2, COLUMN()=$H$1)
6) Format
7) Fill tab.
8) Select red colour.
9) OK
10) OK

Lets breakdown this formula

=AND(ROW()=$H$2, COLUMN()=$H$1)


This formula uses the AND function to check two conditions:

ROW()=$H$2: It checks if the current row number is equal to the value in cell H2.
COLUMN()=$H$1: It checks if the current column number is equal to the value in cell H1.
The formula returns TRUE if both conditions are met, indicating that the cell containing this formula is at the intersection of the row specified in H2 and the column specified in H1. Otherwise, it returns FALSE.

#shorts #short #shortvideo #fyp #excel #microsoft #tiktok #fypシ