Excel Lookups Multiple Columns Concatenated

Question

I have a spreadsheet like this:

| hostname | key  | value |
| host1   | tagA | abc   |
| host1   | tagB | bcd   |
| host1   | tagC | efg   |
| host2   | tagB | hij   |
| host3   | tagB | jkl   |

Elsewhere, I have a single cell: host1. In the following cell, I want to lookup the value for, say host1 tag C.

That is to say, I want to end up with this:

host 1 | efg

If I my lookup fails, it should output something like this (eg. looking up value for tagA of host2):

host2 | #N/A

I've tried using VLOOKUP but I'm getting a #REF! error:

[

Answer 1

the table range in your VLOOKUP function is incorrectly. Change the table range and the column to return to:

=VLOOKUP("host1tagB",A1:D5,4,FALSE)

Answer 2

You can do this without a helper column, if you prefer: