'VLOOKUP as a range
I have a table in a tab with columns: street_id, street_name, street_corner_1 and street_corner_2 (a street corner is the name of the street that intersects it at the beginning or end).
So I have in another tab a table where columns go through street1 to streetn, rows work the same (like a matrix), and I want to check if the name of the street of row 1 (street1) is either the street corner 1 or 2 of street2.
What I wanted to do is format the cell so =COUNTIF(VLOOKUP(street2;street_id:street_corner_2;{3;4}),VLOOKUP(street1;street_id:street_name;2))>0
So, the range for the countif would be the two street corners of street2 and the criteria would be the name of street1, and the cell would be 1 if the name of street1 is among the corners of street2 and 0 otherwise.
I want to define the range with VLOOKUP, how can I do this?
Solution 1:[1]
I made a fake dataser including a street map to see if this what you mean. You want a matrix table with the first and last intersection and check if it's corner 1, corner 2 or nothing.
You can do it with 2 VLOOKUPS. My formula is (note I'm using mixed references so I can use same formula for all columns and rows):
=IF(I$1=$H2;"-";IF(VLOOKUP(I$1;$A$2:$D$5;3;FALSO)=$H2;"Corner 1";IF(VLOOKUP(I$1;$A$2:$D$5;4;FALSO)=$H2;"Corner 2";"-")))
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Foxfire And Burns And Burns |