1

I have a table that looks like this:

A  |   B    | C
---|--------|---
10 | Item x | 1
10 | Item y | 1
10 | Total  | 2
20 | Item x | 4
20 | Item z | 5
20 | Total  | 9

Now I would like to perform a multiple column lookup on this where I give the ID(A) and the Label(B) and get back the Value(C).

I tried this to get value 9 for id 20 and total label:

=FILTER(C:C,A:A=20,B:B="Total")

But I get #N/A no matches found error. What am I doing wrong here? Hint: the Total entry per ID does only exist one or none at all.

1

I was able to successfully execute your constructed formula.

Screenshot

enter image description here

Most probably you have either white spaces before or after the text Total. You can use the following formula to check the length of the text for a column:

=ARRAYFORMULA(LEN(C:C))

Removing those will (hopefully) yield 9. Good luck !!

  • 1
    ok. it wasn't the text, it was the ID. Somehow google thought it is of type text and wasn't able to find it. When I changed the ID column to numeric it worked – BaseBallBatBoy Feb 27 '15 at 12:18
  • 1
    @BaseBallBatBoy For the next time, it is better to share a spreadsheet with us. This way, it would have been clear from the start. Good thing you resolved the problem yourself and welcome on Web Applications !! – Jacob Jan Tuinstra Feb 27 '15 at 12:29

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.