Looking for VLOOKUP Trick

[shdawson]

Hi,

Love VLOOKUP. Has met a lot of needs I have had over the years.

Question:
What do you do when the lookup value is to the RIGHT of the lookup range? In the past, I have referenced the row via =B2, say in column Z. It works, but it can get messy.

Is there another trick out there to overcome this problem?


Thanks,
S

 

[shdawson]

Gentelman,

A question please about this wonderful formula of his.


My Current Formula:

=VLOOKUP(C4,CHOOSE({1,2},Project_Status_History!C$2:C$10000,Project_Status_History!D$2:D$10000),2,0)

This returns #N/A. My value in C4 is the result of a lookup on a different worksheet.

='Driver Table'!A2

The trace window "seems" to be telling me that things work well in the formula, except for this. The lookup seems to go to the Driver Table worksheet and get lost.


Please advise.


Thanks,
S

 

[DonkeyOte]

Used in that way it does not make sense to use the CHOOSE given the VLOOKUP is effectively left to right (ie standard):

=VLOOKUP(C4,Project_Status_History!C$2:D$1000,2,0)

in terms of the issue itself (assuming it's not an issue of looking in the wrong column), what does:

=COUNTIF(Project_Status_History!C2:C1000,C4)

return ?

 

[shdawson]

Thank you for your assistance.


=COUNTIF(Project_Status_History!C$2:C$1000,C4) returns 0


This need started on...
http://www.mrexcel.com/forum/showthread.php?p=2561609#post2561609


So, I will need the MAX function as well. 1 step at a time.


I was trying to hack the provided syntax, but it does appear to not be the way to go.


Please advise.


Thanks,
S

 

[DonkeyOte]

=COUNTIF(Project_Status_History!C$2:C$1000,C4) returns 0

so put simply this means C4 does not exist within the range you're referencing hence #N/A
(it also tells us that the mismatch is not related to differences in data types between criteria and table_array)

Did you meant to use:

=VLOOKUP(C4,CHOOSE({1,2},Project_Status_History!$D$2:$D$1000,Project_Status_History!$C$2:$C$1000),2,0)

by any chance ?

 

[shdawson]

Hmmm....dunno what I meant to use.


Your edited formula returns #N/A as well. I looked at the data. An example project # is 46999. It is in the data range. It is formatted at GENERAL in the original place (and referenced by C4 on our worksheet), and as a NUMBER on Project_Status_History. Would data type be an obstacle here?


Thanks,
S

 

[DonkeyOte]

C4 contains your criteria - check.

In which column is C4 listed on Project_Status_History sheet ?

And further to the above

In which column on Project_Status_History sheet does the data reside that you wish to return as the result (where match found) ?

(from here on in I will refer to the sheet as PSH as I'm tired of typing )

 

[shdawson]

Worksheet:
Project_Status_History

A B C D
Project ID Project Name Report Date Status Indicator



Need to look at the Project ID (A), get the most recent date from Report Date (C), then grab the Status Indicator (D).

I like your PSH trick...I'm tired of typing as well. HA!



Thanks,
S

 

[DonkeyOte]

So I think we're saying that:

a) the criteria C4 is found in Column A on PSH (multiple times potentially)

b) you wish to determine the Max Date amongst all the rows where C4 is found in PSH Col A

c) then you wish to retrieve the Indicator associated with that Max Date

correct ?

If so, questions:

Is the data on PSH sorted in any way ?

If not sorted (per se) can we at least assume that last instance of ID contains the latest date or not ?

Is the intention to retrieve multiple results simultaneously ?
(eg row 5 will contain the results for a different ID (C5 as opposed to C4) using the same PSH data set)

How many rows of data do you have on PSH ?

How frequently does the data on the PSH sheet update ?

Depending on the answers to the above there may be relatively efficient formulae available.
However, equally the required Arrays might be so inefficient that an Intermediate Pivot Table may be worthwhile - from which we can extract the requisite values via GETPIVOTDATA.

I probably won't be able to answer today but will check in tomorrow assuming of course this has not been resolved to your satisfaction in the interim.

 

[schielrn]

This has actually been quite fun to play around with and I might use it to throw some people off :

Excel Workbook
	A	B	C	D	E	F	G	H
1	First Pay	Second Pay	Leader Type			Leader	Second Pay	4.5
2	1	1.5	Pre-rankings Leader			Leader	First Pay	3
3	2	3	New Leader
4	3	4.5	Leader
5	4	6	Reliable Leader
6	5	7.5	Team Leader
7	6	9	Anchor
Sheet1

Cell Formulas
Range	Formula
H1	=VLOOKUP(F1,CHOOSE({1,2,3},$C$2:$C$7,$A$2:$A$7,$B$2:$B$7),IF(G1="First Pay",2,3),0)
H2	=VLOOKUP(F2,CHOOSE({1,2,3},$C$2:$C$7,$A$2:$A$7,$B$2:$B$7),IF(G2="First Pay",2,3),0)

 

[shdawson]

Is the data on PSH sorted in any way ?
Yes, Project ID...ascending.


If not sorted (per se) can we at least assume that last instance of ID contains the latest date or not ?
Please see above.
Also, the most recent row has the most recent date. So, the date row is descending.


Example of Data:

Project ID Project Name Report Date Status Indicator
46137 TEST NAME 12/30/2010 00:00 Green
46137 TEST NAME 12/24/2010 00:00 Green
46137 TEST NAME 12/17/2010 00:00 Red
46137 TEST NAME 12/10/2010 00:00 Red
46137 TEST NAME 11/26/2010 00:00 Red
46137 TEST NAME 11/19/2010 00:00 Yellow
46137 TEST NAME 11/5/2010 00:00 Green
46137 TEST NAME 10/29/2010 00:00 Green
46137 TEST NAME 10/22/2010 00:00 Yellow




Is the intention to retrieve multiple results simultaneously ?
(eg row 5 will contain the results for a different ID (C5 as opposed to C4) using the same PSH data set)
No, will only populate a colum for Project # 46137 with Green, as that is the latest and greatest info on that Project #.

How many rows of data do you have on PSH ?
Several....thousands.

How frequently does the data on the PSH sheet update ?
I export from another system as CSV. Then, paste those values into an XLS worksheet.



Thanks!
S