Lookup from another sheet

**nerf** · 02-09-2012, 11:05 AM

Hi,

I would like to had a sheet auto populated with values form another sheet based on a column vlaue.

This is an example of what I would like to achieve:

SHEET1

	A	B	C	D	E
1	abc	Server1	def	Y	123
2	ghi	Server3	jkl	N	456
3	mno	PC2	opq	Y	789
4	rst	PC5	uvw	Y	012
5	cba	Server4	fed	N	345

SHEET2
A
1 Server1X
2 Server2X
3 PC5X

Sheet 2 should be populated with values from column "B" of sheet1 (and append "X"), if column "D" is = "Y", otherwise ignore and do not add to sheet2.

I was looking at VLOOKUP, but I don't think that it will work... then again, I'm not that good with formulas.

there are more columns and rows, and the data is different... just an example.

can you help? Please no VBA code, only formulas.

Thank you

**NBVC** · 02-09-2012, 11:10 AM

Assuming your data is in Sheet1, starting at A1, then in Sheet2 enter formula:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX(Sheet1!$B$1:$B$5,SMALL(IF(Sheet1!$D$1:$D$5="Y",ROW(Sheet1!$D$1:$D$5)-ROW(Sheet1!$D$1)+1),ROWS($A$2:$A2)))&"X"))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down as far as needed.

Adjust ranges to suit, and reconfirm with CSE keys before copying down again.

**bebo021999** · 02-09-2012, 11:14 AM

Try this in A1, sheet 2:

=INDEX(Sheet1!$B$1:$B$5,SMALL(IF(Sheet1!$D$1:$D$5="Y",ROW(INDIRECT("1:"&ROWS($D$1:$D$5))),""),ROW(A1)))&"X"

Confirmed with Ctrl-Shift-Enter rather than Enter only.
Copy down till getting error sign.

**nerf** · 02-09-2012, 12:18 PM

most excellent. I just notice that you uploaded the excel file... Thank you!

I need to add one more item:
Instead of adding "X", how can I append the value of the corresponding cell from column "E" of sheet1?

SHEET2
A
1 Server1123
2 Server2789
3 PC5012

thanks

**NBVC** · 02-09-2012, 01:09 PM

Try:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX(Sheet1!$B$1:$B$5,SMALL(IF(Sheet1!$D$1:$D$5="Y",ROW(Sheet1!$D$1:$D$5)-ROW(Sheet1!$D$1)+1),ROWS($A$2:$A2)))&INDEX(Sheet1!$E$1:$E$5,SMALL(IF(Sheet1!$D$1:$D$5="Y",ROW(Sheet1!$D$1:$D$5)-ROW(Sheet1!$D$1)+1),ROWS($A$2:$A2)))))

confirmed with CTRL+SHIFT+ENTER

note that you have an extra space in column B entries, that will appear in the results..

**bebo021999** · 02-09-2012, 01:23 PM

@NVBC,
Is it needed to combine the two INDEX?

Concanating two ranges together:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX(Sheet1!$B$1:$B$5&Sheet1!$E$1:$E$5,SMALL(IF(Sheet1!$D$1:$D$5="Y",ROW(Sheet1!$D$1:$D$5)-ROW(Sheet1!$D$1)+1),ROWS($A$2:$A2)))))

keep the formula much shorter!