Forum Discussion

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

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

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
Copper Contributor
Mar 07, 2019
Sorry here are the pictures
- 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?
  - Twifoo
    Silver Contributor
    Mar 07, 2019
    Incidentally, your cited scenario is my “territory”! Please attach your sample file so that I will create a template for you that returns the debits and credits to a selected account, including that account’s opening and closing balances.