ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Vlookup i think .< (https://www.excelbanter.com/excel-worksheet-functions/245861-vlookup-i-think.html)

Vader
Vlookup i think .<
 
I need to look up a value that meets 2 criteria.

My data is like this

..Code.........|Week.....|....Important Data !!
OMG | 1 | XX
AFK | 2 | XX
OMG | 2 | XX
AFK | 3 | XX
OMG | 4 | XX

Where there is no data for a week or code I want a blank cell left in the
template below.

Where there is a match I want the €śImportant Data€ť returned.

Code Code
.......OMG........|.....AFK......
Week1| XX |
Week2| XX | XX
Week3| | XX
Week4| XX |

Im all out of ideas??

Jacob Skaria
Vlookup i think .<
 
With the sample data you have provided try the below formula...Please note
that this is an array formula. You create array formulas in the same way that
you create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula}"

=INDEX($C$2:$C$10,MATCH(1,($A$2:$A$10="OMG")*($B$2 :$B$10=1),0))

'data in sheet1
Col A Col B Col C
Code Week Imp data
OMG 1 a
AFK 2 b
OMG 2 c
AFK 3 d
OMG 4 e

The formula is a bit lengthy after adding error handling to return
blank....With your data in Sheet1; I have tried this in Sheet2 as below..try
the below formula in cell B2.

'in sheet2
Col A Col B Col C
OMG AFK
Week1 a
Week2 c b
Week3 d
Week4 e
Week5


=IF(ISNA(INDEX(Sheet1!$C$2:$C$10,MATCH(1,(Sheet1!$ A$2:$A$10=B$1)*(Sheet1!$B$2:$B$10=--SUBSTITUTE($A2,"Week",)),0))),"",INDEX(Sheet1!$C$2 :$C$10,MATCH(1,(Sheet1!$A$2:$A$10=B$1)*(Sheet1!$B$ 2:$B$10=--SUBSTITUTE($A2,"Week",)),0)))

PS: again an array formula

If this post helps click Yes
---------------
Jacob Skaria


"Vader" wrote:

I need to look up a value that meets 2 criteria.

My data is like this

.Code.........|Week.....|....Important Data !!
OMG | 1 | XX
AFK | 2 | XX
OMG | 2 | XX
AFK | 3 | XX
OMG | 4 | XX

Where there is no data for a week or code I want a blank cell left in the
template below.

Where there is a match I want the €śImportant Data€ť returned.

Code Code
.......OMG........|.....AFK......
Week1| XX |
Week2| XX | XX
Week3| | XX
Week4| XX |

Im all out of ideas??

T. Valko
Vlookup i think .<
 
The formula is a bit lengthy after adding error
handling to return blank....

If the data being returned is TEXT (array entered):

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX($C$2:$ C$10,MATCH(1,($A$2:$A$10="OMG")*($B$2:$B$10=1),0)) ))

Or, possibly (array entered):

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX($C$2:$C$10,M ATCH(1,($A$2:$A$10="OMG")*($B$2:$B$10=1),0))))

=IF(ISNA(INDEX(Sheet1!$C$2:$C$10,MATCH(1...

In these cases there's no need to include INDEX in the error trap. The
portion of the formula that will generate the error is MATCH(1...

So this should suffice:

=IF(ISNA(MATCH(1...

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
With the sample data you have provided try the below formula...Please note
that this is an array formula. You create array formulas in the same way
that
you create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula}"

=INDEX($C$2:$C$10,MATCH(1,($A$2:$A$10="OMG")*($B$2 :$B$10=1),0))

'data in sheet1
Col A Col B Col C
Code Week Imp data
OMG 1 a
AFK 2 b
OMG 2 c
AFK 3 d
OMG 4 e

The formula is a bit lengthy after adding error handling to return
blank....With your data in Sheet1; I have tried this in Sheet2 as
below..try
the below formula in cell B2.

'in sheet2
Col A Col B Col C
OMG AFK
Week1 a
Week2 c b
Week3 d
Week4 e
Week5


=IF(ISNA(INDEX(Sheet1!$C$2:$C$10,MATCH(1,(Sheet1!$ A$2:$A$10=B$1)*(Sheet1!$B$2:$B$10=--SUBSTITUTE($A2,"Week",)),0))),"",INDEX(Sheet1!$C$2 :$C$10,MATCH(1,(Sheet1!$A$2:$A$10=B$1)*(Sheet1!$B$ 2:$B$10=--SUBSTITUTE($A2,"Week",)),0)))

PS: again an array formula

If this post helps click Yes
---------------
Jacob Skaria


"Vader" wrote:

I need to look up a value that meets 2 criteria.

My data is like this

.Code.........|Week.....|....Important Data !!
OMG | 1 | XX
AFK | 2 | XX
OMG | 2 | XX
AFK | 3 | XX
OMG | 4 | XX

Where there is no data for a week or code I want a blank cell left in the
template below.

Where there is a match I want the "Important Data" returned.

Code Code
.......OMG........|.....AFK......
Week1| XX |
Week2| XX | XX
Week3| | XX
Week4| XX |

Im all out of ideas??

Ashish Vaidya
Vlookup i think .<
 
I usually use the error checking method so the formula I use is like this:

=IF(ISERROR(vlookup(B2,data_range,3,false)),"",vlo okup(b2,data_range,3,false))

It tries to do the vlookup based on the criteria you want, if there is
nothing to return, your vlookup will return an error so I use the IF(ISERROR
routein to enter a null ("") when the vlookup fails. If the vllokup formula
is not in error, then it will return whatever it was supposed to return from
the data_range.

I'm sorry I'm not good at explaining but I hope you get the gist of what I
was doing with the error checking routein.
Good Luck.

Ashish


"Vader" wrote:

I need to look up a value that meets 2 criteria.

My data is like this

.Code.........|Week.....|....Important Data !!
OMG | 1 | XX
AFK | 2 | XX
OMG | 2 | XX
AFK | 3 | XX
OMG | 4 | XX

Where there is no data for a week or code I want a blank cell left in the
template below.

Where there is a match I want the €śImportant Data€ť returned.

Code Code
.......OMG........|.....AFK......
Week1| XX |
Week2| XX | XX
Week3| | XX
Week4| XX |

Im all out of ideas??

Vader
Vlookup i think .<
 
Thanks Jacob,

The formula works prefect,

But i have 1 more question now.

In a cell i need to add up 2 other cells + the result of the formula.
eg . in a cell i have "=B2+C2+E2" the result i get is #VALUE!
B2= a number
C2= a number
E2= the formula which could be either a number it has found or blank.

Do you have any suggestions?

Vader


"Jacob Skaria" wrote:

With the sample data you have provided try the below formula...Please note
that this is an array formula. You create array formulas in the same way that
you create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula}"

=INDEX($C$2:$C$10,MATCH(1,($A$2:$A$10="OMG")*($B$2 :$B$10=1),0))

'data in sheet1
Col A Col B Col C
Code Week Imp data
OMG 1 a
AFK 2 b
OMG 2 c
AFK 3 d
OMG 4 e

The formula is a bit lengthy after adding error handling to return
blank....With your data in Sheet1; I have tried this in Sheet2 as below..try
the below formula in cell B2.

'in sheet2
Col A Col B Col C
OMG AFK
Week1 a
Week2 c b
Week3 d
Week4 e
Week5


=IF(ISNA(INDEX(Sheet1!$C$2:$C$10,MATCH(1,(Sheet1!$ A$2:$A$10=B$1)*(Sheet1!$B$2:$B$10=--SUBSTITUTE($A2,"Week",)),0))),"",INDEX(Sheet1!$C$2 :$C$10,MATCH(1,(Sheet1!$A$2:$A$10=B$1)*(Sheet1!$B$ 2:$B$10=--SUBSTITUTE($A2,"Week",)),0)))

PS: again an array formula

If this post helps click Yes
---------------
Jacob Skaria


"Vader" wrote:

I need to look up a value that meets 2 criteria.

My data is like this

.Code.........|Week.....|....Important Data !!
OMG | 1 | XX
AFK | 2 | XX
OMG | 2 | XX
AFK | 3 | XX
OMG | 4 | XX

Where there is no data for a week or code I want a blank cell left in the
template below.

Where there is a match I want the €śImportant Data€ť returned.

Code Code
.......OMG........|.....AFK......
Week1| XX |
Week2| XX | XX
Week3| | XX
Week4| XX |

Im all out of ideas??

T. Valko
Vlookup i think .<
 
in a cell i have "=B2+C2+E2" the result i get is #VALUE!

Use the SUM function:

=SUM(B2,C2,E2)

The SUM function ignores text which is what a formula blank ("") is.

--
Biff
Microsoft Excel MVP


"Vader" wrote in message
...
Thanks Jacob,

The formula works prefect,

But i have 1 more question now.

In a cell i need to add up 2 other cells + the result of the formula.
eg . in a cell i have "=B2+C2+E2" the result i get is #VALUE!
B2= a number
C2= a number
E2= the formula which could be either a number it has found or blank.

Do you have any suggestions?

Vader


"Jacob Skaria" wrote:

With the sample data you have provided try the below formula...Please
note
that this is an array formula. You create array formulas in the same way
that
you create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula. If successful in 'Formula Bar' you can notice the curly braces
at
both ends like "{=<formula}"

=INDEX($C$2:$C$10,MATCH(1,($A$2:$A$10="OMG")*($B$2 :$B$10=1),0))

'data in sheet1
Col A Col B Col C
Code Week Imp data
OMG 1 a
AFK 2 b
OMG 2 c
AFK 3 d
OMG 4 e

The formula is a bit lengthy after adding error handling to return
blank....With your data in Sheet1; I have tried this in Sheet2 as
below..try
the below formula in cell B2.

'in sheet2
Col A Col B Col C
OMG AFK
Week1 a
Week2 c b
Week3 d
Week4 e
Week5


=IF(ISNA(INDEX(Sheet1!$C$2:$C$10,MATCH(1,(Sheet1!$ A$2:$A$10=B$1)*(Sheet1!$B$2:$B$10=--SUBSTITUTE($A2,"Week",)),0))),"",INDEX(Sheet1!$C$2 :$C$10,MATCH(1,(Sheet1!$A$2:$A$10=B$1)*(Sheet1!$B$ 2:$B$10=--SUBSTITUTE($A2,"Week",)),0)))

PS: again an array formula

If this post helps click Yes
---------------
Jacob Skaria


"Vader" wrote:

I need to look up a value that meets 2 criteria.

My data is like this

.Code.........|Week.....|....Important Data !!
OMG | 1 | XX
AFK | 2 | XX
OMG | 2 | XX
AFK | 3 | XX
OMG | 4 | XX

Where there is no data for a week or code I want a blank cell left in
the
template below.

Where there is a match I want the "Important Data" returned.

Code Code
.......OMG........|.....AFK......
Week1| XX |
Week2| XX | XX
Week3| | XX
Week4| XX |

Im all out of ideas??

Vader
Vlookup i think .<
 
Ok 1 last update on this formula.

Im using Jacobs formula

=IF(ISNA(INDEX(Sheet1!$C$2:$C$10,MATCH(1,(Sheet1!$ A$2:$A$10=B$1)*(Sheet1!$B$2:$B$10=--SUBSTITUTE($A2,"Week",)),0))),"",INDEX(Sheet1!$C$2 :$C$10,MATCH(1,(Sheet1!$A$2:$A$10=B$1)*(Sheet1!$B$ 2:$B$10=--SUBSTITUTE($A2,"Week",)),0)))

But i also need the formula to add up the number it finds in coloum C if
their are 2 hits.

I used leters in the example but the important date being found is a numbe
or 2.


"Jacob Skaria" wrote:

With the sample data you have provided try the below formula...Please note
that this is an array formula. You create array formulas in the same way that
you create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula}"

=INDEX($C$2:$C$10,MATCH(1,($A$2:$A$10="OMG")*($B$2 :$B$10=1),0))

'data in sheet1
Col A Col B Col C
Code Week Imp data
OMG 1 a
AFK 2 b
OMG 2 c
AFK 3 d
OMG 4 e

The formula is a bit lengthy after adding error handling to return
blank....With your data in Sheet1; I have tried this in Sheet2 as below..try
the below formula in cell B2.

'in sheet2
Col A Col B Col C
OMG AFK
Week1 a
Week2 c b
Week3 d
Week4 e
Week5


=IF(ISNA(INDEX(Sheet1!$C$2:$C$10,MATCH(1,(Sheet1!$ A$2:$A$10=B$1)*(Sheet1!$B$2:$B$10=--SUBSTITUTE($A2,"Week",)),0))),"",INDEX(Sheet1!$C$2 :$C$10,MATCH(1,(Sheet1!$A$2:$A$10=B$1)*(Sheet1!$B$ 2:$B$10=--SUBSTITUTE($A2,"Week",)),0)))

PS: again an array formula

If this post helps click Yes
---------------
Jacob Skaria


"Vader" wrote:

I need to look up a value that meets 2 criteria.

My data is like this

.Code.........|Week.....|....Important Data !!
OMG | 1 | XX
AFK | 2 | XX
OMG | 2 | XX
AFK | 3 | XX
OMG | 4 | XX

Where there is no data for a week or code I want a blank cell left in the
template below.

Where there is a match I want the €śImportant Data€ť returned.

Code Code
.......OMG........|.....AFK......
Week1| XX |
Week2| XX | XX
Week3| | XX
Week4| XX |

Im all out of ideas??


All times are GMT +1. The time now is 08:43 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com