I have a virtual array table as follows. It is generated from query, not from the cell:
| Col1 | Col2 | Col3 |
|---|---|---|
| A | E | F |
| Q | B | N |
| *** | *** | *** |
| T | Y | I |
| R | H | J |
| W | X | M |
| *** | *** | *** |
| G | L | K |
| A | O | P |
| *** | *** | *** |
I would like to concatenate the value of Col2 between each “***” and use the concatenated value to lookup for the final value. Here is my anticipated result.
Anticipated Result
| Col1 | Col2 | Col3 | Col4 |
|---|---|---|---|
| A | E | F | APPLE |
| Q | B | N | |
| *** | *** | *** | |
| T | Y | I | ORANGE |
| R | H | J | |
| W | X | M | |
| *** | *** | *** | |
| G | L | K | BANANA |
| A | O | P | |
| *** | *** | *** |
Lookup Table of Col2 to Col4
| ColX | ColY |
|---|---|
| EB | APPLE |
| YHX | ORANGE |
| LO | BANANA |
What I tried
I tried to search for offset function. But I don’t know how to handle the “cell_reference” in offset function, because the array table is virtually created by query. I also have difficulties in running the vlookup function through an virtually created array table.
Thanks for helping.
game01 gamer is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.