• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multi VLookup issues

P

phillic004

New Member
I'm trying to use the below variables to determine construction level column. Correct answer is BAL-29 as bolded. Created a concatenation script with nested VLookup but cannot get it to work. =VLOOKUP(K2 & "|" & L2 & "|" & M2,A2:G61,7). I have also tried nested If statements, match statements etc with no luck. Any help will be greatly appreciated

  • Variables

    dist calc bushland fdi slope

    40 forest 100 10

    Data

Vegetation_Type FDI Slope_Max Distance_Min Distance_Max Construction_Level Concat

Forest 100 0 0 18 BAL-FZ Forest|100|0

Forest 100 0 19 24 BAL-40 Forest|100|0

Forest 100 0 25 34 BAL-29 Forest|100|0

Forest 100 0 35 47 BAL-19 Forest|100|0

Forest 100 0 48 99 BAL-12.5 Forest|100|0

Forest 100 0 100 1000 BAL LOW Forest|100|0

Forest 100 5 0 24 BAL-FZ Forest|100|5

Forest 100 5 25 31 BAL-40 Forest|100|5

Forest 100 5 32 42 BAL-29 Forest|100|5

Forest 100 5 43 56 BAL-19 Forest|100|5

Forest 100 5 57 99 BAL-12.5 Forest|100|5

Forest 100 5 100 1000 BAL LOW Forest|100|5

Forest 100 10 0 30 BAL-FZ Forest|100|10

Forest 100 10 31 38 BAL-40 Forest|100|10

Forest 100 10 39 52 BAL-29 Forest|100|10

Forest 100 10 53 68 BAL-19 Forest|100|10

Forest 100 10 69 99 BAL-12.5 Forest|100|10

Forest 100 10 100 1000 BAL LOW Forest|100|10
 
I can probably help if you upload the file somewhere. I'm guessing you're making a rather obvious mistake in this case. It looks like your concatenated field is on the right and vlookup can't pull data from the left.
 
Thanks for your reply dan_l. An excel spreadsheet showing the data is at the following link. I moved the concatenated field to the left but I'm still unsure why it is not working.


http://www.funnelwebdevelopment.com.au/question_vlookup_2.xls
 
=VLOOKUP(L2&"|"&M2&"|"&N2,A2:G61,VLOOKUP(K2,A2:G61,7))


It's syntax:


=vlookup(findwhat,findwhere,which-column-should-I-get)


In this case which-column-should-I-get is a bad parameter, because the result of the second vlookup isn't going to be a column number. Here's what's going on with that second vlookup:


When you give it "findwhere", the index always has to be on the left hand side of the data. So instead of:


VLOOKUP(K2,A2:G61,7)


It should be:

VLOOKUP(K2,E2:G61,3)


Which does (in this case) return your desired result.


Now here's what I think you're trying to do: you've got basically 2 stages of lookup going on here. One is dependent upon forest| 100 | 10 the other is based on your distance.


We can do this, I just want to be sure that's what you're trying to do before I get going:)
 
Yep that's exactly what I'm trying to do, but how does the nesting syntax go to combine the two? I tried to the comcatenated values then the distance but it kept returning errors. Thanks again for your help.
 
Ok here's what I did:


1. I concatenated forest|fd|slope. You don't have to do it on your own, but it sure makes this example a lot easier to digest. It's in o2


2. I made sure the list was sorted. If you're going to use this method, the list _has_ to be sorted by column a, the concatenated field.


3. I created a dynamic named range that refers to:


=OFFSET($A$1,MATCH($O$2,$A$1:$A$1000,0),4,COUNTIF($A$1:$A$1000,$o$2)-1,3)


and called it relevantrange.


4. I adjusted your second vlookup to formula:


=VLOOKUP(K2,RelevantRange,3,TRUE)


I can't upload it at the moment because dropbox is down, but when it comes back up for me, I'll post it. In the meantime, (and in case you're in a pinch), here's how it works:


=offset(startwhere,rowssdown,columnsover,height,width)


startwhere: $a$1


rowsdown: MATCH($O$2,$A$1:$A$1000,1) or, the FIRST time it find our concatenated value in column A, but it's only returning the row number relative to A1 (above)


columnsover: From column A (in start where), the number of columns over where the range should start. In this case, going 4 over puts you in the distance column.


------

I recommend just putting that first part in a cell like =OFFSET($A$1,MATCH($O$2,$A$1:$A$1000,1),4) just to see it. It will return the distance associated with the first row matching your concatenated value)

------


Height: COUNTIF($A$1:$A$1000,$o$2)-1 The height is determined by the number of matches in our concatenation.


Width: Fixed value at 3; gets us to the last column of the data.


So basically what we've done is created a range where the location and size is moving based on the concatenation of the 3 values at the top. The second vlookup will then only look at those entries.


Here's chandoo on dynamic sized ranges:

http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/
 
Heh. thanks.


Named ranges are really important. Just the garden variety save your sanity in not having to type mega formulas with often long absolute references. The dynamic ones (like this one) expand your tool kit exponentially.
 
I want to check multiple answer in Vlookup is it possible ?

I am working on one excel sheet in which there are range of medicine and batch number. I want to use Vlookup in so , if i enter one product like XYZ and I will get all the batch number like 001, 002, 003, etc.

At the moment I am getting batch number but it is first among all batch.


Please suggest me any Vlookup related formula which can help me to sort out this ?

Thank you
 
Shreycool5


Welcome to the Chandoo.org Forums


Can you please read the Posting Rules and Etiquette at: http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting


Please don't add questions to the end of even similar posts, start a new post every time.

New posts can be made here: http://chandoo.org/forums/?new=1
 
You must log in or register to reply here.
Back
Top