Forum Discussion

Kianzad's avatar
Kianzad
Copper Contributor
Mar 06, 2019

VLOOKUP help

Hello! I want to make a cell in H1, where within a certain matrix I've made, whenever the number 1 appears in row E, the cell H1 should copy the number in row C. This I know how to do by now.   How...
excel
Twifoo's avatar
Twifoo
Silver Contributor
Mar 07, 2019
Assuming your Column Labels are in Row 1 and your data are in Rows 2:10, the formula in H2, copied down to H10, is:
=IFERROR(LOOKUP(PI(),2/(1/ROW(C$2:C$10)=MAX(INDEX(
1/ROW(C$2:C$10)*(E$2:E$10=1)*(COUNTIF(H$1:H1,C$2:C$10)=0),0))),
C$2:C$10),"")
  • Kianzad's avatar
    Kianzad
    Copper Contributor
    Mar 07, 2019

    Thanks alot for your response, I have tried this formula and it seems to be working exactly like wanted! I have just 1 problem left, it seems to be adding a value of 0 in each of the sides I've put it. (I've changed the code a tiny bit, to make it fit my needs. I have tried attaching pictures of what it looks like, with the formula where there the number 0 appears. Hope you can help me! Thanks alot again! :-)

    • Kianzad's avatar
      Kianzad
      Copper Contributor
      Mar 07, 2019

      Sorry here are the pictures

      • Kianzad's avatar
        Kianzad
        Copper Contributor
        Mar 07, 2019

        Also, I have one last question here - I would like to do so that the Account name automatically changes depending on what account name is referenced as 1. Therefore I tried to do: "=VLOOKUP(1;A:D;1;FALSE)" Indicating it should give me the text one the same row as the number 1, however I get an error. I believe it's because the number 1 occurs more than once, and I'm only asking for one value. Therefore I would like for it to add only the text from the first time 1 occurs, or any of the times it occurs, just only once. Is that possible?

Resources