Forum Discussion
excel
Hello.
I Wanted to find a way to solve this and would appreciate all the help. here I have two columns in different sheets and i want to put them in one sheet correspondingly. the thing is when i use VLOOKUP the numbers in the second sheet that corresponds to blanks are disregarded while what i want is 2 columns corresponding while skipping the blanks
1-1
2-2
3-3
4-4 (NOT 4-6)
5-5 (Not 5-7)... etc
=IFERROR(INDEX(Tabelle2!$A$1:$A$10,SMALL(ROW(Tabelle2!$A$1:$A$10),A1)),"")Does this return the expected result? Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
8 Replies
- =IF(A1="","",VLOOKUP(A1,Sheet2!A1:A10,1,0))
- I realised this only works if both data in both sheets are the same value, Is there any other way??
I' nut sure what you want. But you can try this to get a list of all the values in column A in either Sheet1 or Sheet2
=LET(list,VSTACK(Sheet1!A1:A100,Sheet2!A1:A100),UNIQUE(FILTER(list,list<>"")))VSTACK() stacks the values from the A-column in both sheet1 and sheet2. Filter and unique removes duplicates and empty cells
If you put the formula above in cell C2 you can use this i D2 (to get values from Sheet1)
=XLOOKUP(C2#,Sheet1!A1:A100,Sheet1!A1:A100,"")And this in E2 (to get values from Sheet2)
=XLOOKUP(C2#,Sheet2!A1:A100,Sheet2!A1:A100,"")C2# means we want the formula to fill down as many rows as the result in C2
or use COUNTIF() to get a count instead (if there are duplicates)
=COUNTIF(Sheet1!A1:A100,C2#)
=COUNTIF(Sheet2!A1:A100,C2#)I'm not sure if i understand what you wants. Ask again if i missunderstand your question.
- This worked thank u so much^^
=IFERROR(INDEX(Tabelle2!$A$1:$A$10,SMALL(ROW(Tabelle2!$A$1:$A$10),A1)),"")Does this return the expected result? Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
- When I try this formula it just returns the first cell and all the other cells return blank so what am I doing wrong??
In the attached file i entered the formula in cell B1 as an arrayformula with ctrl+shift+enter. Then i draged the formula down to cell B15 and it returns the expected results.
