I get formula error.
Why did you create column A, just to concatenate column B and C? I don't need exactly that, I need only to search and compare.


DarthCoin

By "Drag" I mean the fact that you can click on the bottom right of the cell and "drag" the formula down.  This will update the cell references accordingly and display the corresponding values.

https://paste.nostrdev.com/?d557826675410582#BBFDzTe8txYBzY9xhHNfiNvRNtPVZr4kvVACPCrnNfdN

devs

I need an Excel formula

Does this make sense?


| LKP               | ID   | Service | Data  |      |                                   |                    |     |     |     |
|-------------------|------|---------|-------|------|-----------------------------------|--------------------|-----|-----|-----|
| =B2&"\|"&C2      | 1234 | AAA     | 4032  |      | AAA                               | BBB                | CCC | DDD | EEE |
| ^^ Drag this DOWN | 1234 | BBB     | 145   | 1234 | =VLOOKUP($E3&"\|"&F$2,$A:$D,4,0) | |     |     |     |
|                   | 1234 | CCC     | 136,5 | 456  | ^^ Drag this down, then drag them all across                 |                    |     |     |     |
|                   | 1234 | DDD     | 48    | 658  |                                   |                    |     |     |     |
|                   | 1234 | EEE     | 56    |      |                                   |                    |     |     |     |
|                   | 456  |         |       |      |                                   |                    |     |     |     |
|                   | 456  |         |       |      |                                   |                    |     |     |     |
|                   | 658  |         |       |      |                                   |                    |     |     |     |
|                   | 658  |         |       |      |                                   |                    |     |     |     |

By "Drag" I mean the fact that you can click on the bottom right of the cell and "drag" the formula down.  This will update the cell references accordingly and display the corresponding values.

Here is a copy of the spreadsheet:  https://paste.nostrdev.com/?d557826675410582#BBFDzTe8txYBzY9xhHNfiNvRNtPVZr4kvVACPCrnNfdN


LKP	ID	Service	Data
=B2&"\|"&C2	1234	AAA	4032		AAA	BBB	CCC	DDD	EEE
^^ Drag this DOWN	1234	BBB	145	1234	=VLOOKUP( $E3&"\\|"&F$ 2, $A :$ D,4,0)
	1234	CCC	136,5	456	^^ Drag this down, then drag them all across
	1234	DDD	48	658
	1234	EEE	56
	456
	456
	658
	658