Lookup and Populate

[Ant123]

Hi All,

I am still new to this and trygin to learn as much as possible so I was hoping that someone might be able to help me with another query.

I have a list of 'TRUE' 'FALSE' or blanks in column E, and 4 character data in column B. I would like to copy exactly, any data from column B that corresponds to 'FALSE' in column E into column F.

I think I need to do something similar to below but I am not sure how to write it. I would be really grateful for any feedback :-)

=VLOOKUP(FALSE, E2:E1000, INDEX, Column B, 0)

Once I have the copied data in column F, I then need to get rid of any gaps so that it will no longer correspond to column B.

Thank you :-)

[alansidman]

Are the Trues and Falses the result of a formula calculation or text?

A sample spreadsheet attached would help

[Ant123]

Hi Alansidman,

Yes, the TRUES and FALSES are from: = IF(D2=TRUE, "", IF( LEFT(B2, 1) = LEFT(C2, 1), TRUE, FALSE))

Example1.xlsx

Please see example above.

Thank you

[alansidman]

This will populate column F


=IF(E1=FALSE,B1,"")

but I don't understand this statement:

Once I have the copied data in column F, I then need to get rid of any gaps so that it will no longer correspond to column B.

[Ant123]

Thank you - that's great. I mean that I would like to get rid of all the blank cells in the column - I thought I could do it by F5 - blanks - but this does not seem to work.

[alansidman]

I am still a bit confused. You want to get rid of blank cells or the rows that contain blank cells. If the latter, then rows based upon blanks in which column. Your explanation is not quite clear enough.

[AlKey]

Maybe this?

In F1

Array formula must be confirmed with Ctrl+Shift+Enter key combination

=IFERROR(INDEX($B$1:$B$7,SMALL(IF($E$1:$E$7=FALSE,ROW($E$1:$E$7),""),ROWS($A$1:A1))),"")

	A	B	C	D	E	F
1	1	J876	J876	TRUE		B543
2	1	GG55	GG55	TRUE
3	1	BL13	BL13	TRUE
4	1	GT35	GT35	TRUE
5	1	IF56	IF56	TRUE
6	1	B543	U764	FALSE	FALSE
7	1	6897	6879	FALSE	TRUE

[Ant123]

Sorry I am not explaining well. My column F now reads: F1: blank, F2: blank, F3: blank, F4: blank, F5: blank, F6: B543, F7: blank etc.... (I have alot more data than on the example). I would like to get rid of the 'blanks' so that it reads: F1: B543, etc.

I hope this helps.

[alansidman]

I think that Alkey now has you covered on that.

[Ant123]

Hi AlKey,

Thank you for the input I have tried this but it does not seem to work. Also, I am afraid I don't really understand what the formula means - sorry I am new to this!

[AlKey]

Follow these instructions

1. Copy this formula and paste it cell F1 and press Enter.
2. Press key F2 on your keyboard
3. Press and hold Ctrl and Shift together and while holding them press Enter key.
4. Drag formula down.

[Ant123]

Got it that's great - thank you. Would you mind explaining the formula so that I know what its doing and hopefully can repeat in future? :-)

[AlKey]

Basically, the formula looks in column E for 'FALSE' and returns the row number. Then it matches it with INDEX of column B and returns its value.


You're welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

[Ant123]

Thank you :-) Will do