Vlookup i think >.<

  • Thread starter Thread starter Vader
  • Start date Start date
V

Vader

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??
 
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
 
The formula is a bit lengthy after adding error
handling to return blank....
Click to expand...

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,MATCH(1,($A$2:$A$10="OMG")*($B$2:$B$10=1),0))))
=IF(ISNA(INDEX(Sheet1!$C$2:$C$10,MATCH(1...
Click to expand...

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...
 
I usually use the error checking method so the formula I use is like this:

=IF(ISERROR(vlookup(B2,data_range,3,false)),"",vlookup(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
 
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
 
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.
 
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.
 
Back
Top