Conditonal VLookup or IF function?

Abovemypaygrade

New Member
Joined
Jun 6, 2013
Messages
12
Hi guys,

Im pretty new to the whole vlookup functions of excel, so what im asking may not be achievable with that function in particular but its what i think is most accurate.

I have a spreadsheet which i need to get data with a certain criteria off and onto another spreadsheet.
A simplified version is:
Customer Reference Customer Name Red Blue


I want to search by Red and if there is a y in that column then it will bring across the customer reference and the customer name onto the new spreadsheet.

I only want the Customer details for the customers which have a y in the red column. I'd prefer it to be a formula as there may be future customers that will apply.

Is that possible with a vlookup?
Am i completely wrong and it should be an IF function, ie IF red = y then copy Customer Reference and Customer Name.

Sorry for being all over the place like i mentioned pretty new to all these more advanced functions of excel.

Thanks for any help.
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,985
in the second spreadsheet.say in A1 ...........if(sheet1!C1="red",sheet1!A1,"")
in B1 in the second spreadsheet the same formula but sheet1!B1,"") at the end
 

Abovemypaygrade

New Member
Joined
Jun 6, 2013
Messages
12
So it does seem that a simple IF function will suffice. However when applying the formula above i have to have it go down 800 odd rows (the length of my orginal list) in order to count the "red" along that row. This means there are huge gaps.

Is there anyway to refine the formula so that instead of checking each line at a time. It finds all the "red" and returns them together. So i can have a continuous block instead of all the spaces inbetween.

Unfortunately im at work and in a controlled environment and cant even download the spreadsheet uploading program to better explain what i mean.

thanks again for any help.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,319
You need an array formula. If you are not familiar with array formulas take a look at
Array Formulas

See if this example helps

Sheet1

A B C D (headers in row 1)
Custom Reference
Custom Name
Red
Blue
Ref1
Name1
Y
Y
Ref2
Name2
N
N
Ref3
Name3
N
N
Ref4
Name4
Y
N
Ref5
Name5
Y
Y
Ref6
Name6
Y
Y
Ref7
Name7
N
Y
Ref8
Name8
N
Y
Ref9
Name9
Y
Y
Ref10
Name10
N
N

<tbody>
</tbody>


Sheet2

A B C D (headers in row 1)
Custom Reference
Custom Name
Counting
Ref1
Name1
5
Ref4
Name4
Ref5
Name5
Ref6
Name6
Ref9
Name9

<tbody>
</tbody>


Formula in D2
=COUNTIF(Sheet1!C:C,"Y")

Array formula in A2
=IF($D$2>=ROWS(A$2:A2),INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$C$2:$C$1000="Y",ROW(Sheet1!$C$2:$C$1000)),ROWS(A$2:A2))),"")

confirmed with Ctrl+Shift+Enter, not just Enter
(hold down both Ctrl and Shift keys and hit Enter)

copy across to B2 and down

M.
 

Abovemypaygrade

New Member
Joined
Jun 6, 2013
Messages
12
Thats great thanks.
I have managed to get it to work however im not sure if its a limitation with the formula or im doing it wrong but essentially i want to apply the said formula to a different column.

So if we were to continue with the example above.
And say that there are 7 different colours and so on my spreadsheet i want to include all of the Red and Blue, whats the best way to do that?
I was proposing that I replace all of the references to column C to column D and put that in cells after all the results have been returned using the first formula.
However i am just getting blank formulas. I have done a count if for all of the "blue" and changed my reference to look at the new cell where this countif is, but still just getting blank.

Hopefully whatever the solution ill be able to apply it to other sheets in the workbook.

My project at work amalgamates 2 columns on 4 worksheets.

Thanks in advance for any help.

A.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,319
Sorry, it's not clear for me what you want.
Could you post a data sample and expected results?

M.
 

Abovemypaygrade

New Member
Joined
Jun 6, 2013
Messages
12
Client Ref.Client NameRedBlueGreenYellowOrange
123XYZy
124WYZy
125ALDy
126KGOy
127HSKy
....y
....y
....y
....y

<tbody>
</tbody>

Imagine the above table represents one of my worksheets. Unique Client references on the left and then various pieces of information along various columns.
Using your formula in a previous post I was able to get all of the Client reference numbers for clients with a "y" in column Red (Formula Red)
I tried adapting your formula so that it would look for "y" in column Blue (Formula Blue) and put those below all the first ones found using the Formula Red.
However all i get is blank cells, no errors just blank cells.
I did use the cell which as a Countif on to find the total "y" in a said column and have used that in my formula but to no avail.

Please find below the formulas i have used.

Formula Red=IF($P$2>=ROWS(A$3:A3),INDEX('All Clients'!A:A,SMALL(IF('All Clients'!$AY$3:$AY$900="y",ROW('All Clients'!$AY$3:$AY$900)),ROWS(A$3:A3))),"")

Formula Blue=IF($P$25>=ROWS(A$3:A28),INDEX('All Clients'!A:A,SMALL(IF('All Clients'!$AX$3:$AX$900="y",ROW('All Clients'!$AX$3:$AX$900)),ROWS(A$3:A28))),"")

I have 4 sheets which each have 2 columns for which i am trying to get the data all together on 1 sheet.

Thanks for any help
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,319
Using your data sample above (assumed at columns A:G headers in row 1)

A B C D E F G
ClientRef.
ClientName
Red
Blue
Green
Yellow
Orange
123
XYZ
y
124
WYZ
y
125
ALD
y
126
KGO
y
127
HSK
y
128
DFR
y
129
FGR
y
130
GTH
y
131
WTU
y

<TBODY>
</TBODY>


Maybe this

I J K L M N (headers in row 1)
Color
Count"Y"
Result1
Result2
Result3
Result4
Red
2
XYZ
KGO
Blue
2
DFR
WTU
Green
2
ALD
HSK
Yellow
2
WYZ
FGR
Orange
1
GTH

<TBODY>
</TBODY>

Formula in J2
=COUNTIF(INDEX(A:G,0,MATCH($I2,$1:$1,0)),"Y")
copy down

Array formula in K2
=IF($J2>=COLUMNS($K2:K2),INDEX($B:$B,SMALL(IF(INDEX($A$1:$G$10,0,MATCH($I2,$1:$1,0))="Y",ROW($B$1:$B$10)),COLUMNS($K2:K2))),"")

confirmed with Ctrl+Shift+Enter, not just Enter

copy across and down

M.
 

Forum statistics

Threads
1,082,601
Messages
5,366,571
Members
400,902
Latest member
fathima

Some videos you may like

This Week's Hot Topics

Top