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:


  • Try to use SUMPRODUCT function.
    – Lee
    Feb 26 '19 at 9:53

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


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

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.