Forum Discussion

Copper Contributor

Mar 09, 2023

Solved

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 ...

BI & Data Analysis

excel

Formulas and Functions

OliverScheurich
Mar 09, 2023
Yomnaehab
=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.

MindreVetande

Iron Contributor

Mar 09, 2023

=IF(A1="","",VLOOKUP(A1,Sheet2!A1:A10,1,0))

Yomnaehab

Copper Contributor

Mar 14, 2023

I realised this only works if both data in both sheets are the same value, Is there any other way??

MindreVetande
Iron Contributor
Mar 14, 2023
Yomnaehab
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.