VLOOKUP for 2 values of the same locations

[Soemd]

Hello,

I am looking to reference a list of locations by station, Ex. 6289+00. (typed as 628900 and set to format with the '+')
my reference table is set up:

begin	end	length	lot number
6277+00	6288+00	1100	1
6288+00	6303+00	1500	2

the function I use is:

Please Login or Register  to view this content.

The first VLOOKUP is to check to make sure the location (G8) is within the bounds of the lot. The second VLOOKUP returns the lot number. Some of the Lots fall within the same station ranges of other lots dependent on workable areas and whether they are on the left side or the right side of the roadway. Is there a way to search through the reference table for the exact number if the ranges are the same but the side is different?

for example:

begin	end	project R or L	Length	Lot #
6275+00	6281+00	R	600	3
6277+00	6288+00	L	1100	1
6281+00	6288+00	R	700	4
6288+00	6303+00	L	1500	2
6288+00	6303+00	R	1500	5

Thank you for any input! Any ideas will get the problem solving 'juices' flowing.

[XOR LX]

Hi,

In which group would you categorise those that match precisely either the 'Begin' or 'End' figure? For example, which Lot is correct for 6288+00 R - 4 or 5?

Regards

[JBeaucaire]

Instead of a VLOOKUP, to find a value in one column and return a value to the LEFT you would use INDEX / MATCH.

For example
=INDEX(A:A, MATCH("Cat", $B:$B, 0))

[Soemd]

XOR LX, I would categorize them into the begining range and not into the ending.

JBeaucaire,

That works just like VLOOKUP, except it does more, fantastic! I still need it to be able to check multiple references looking at the approximate MATCH and then checking whether it fits on the Right of Left. I am unsure how an If statement would work but I've tried a few variants. Possibly looking for a way to use MATCH to restrict the rows searched by INDEX to either the right or left reference cells?

Thus so far the function is operating the same as VLOOKUP with:

Please Login or Register  to view this content.

[JBeaucaire]

You are correct, people start out with VLOOKUP and HLOOKUP and then migrate to INDEX/MATCH and INDEX/MATCH/MATCH.

INDEX/MATCH on its own can replace VLOOKUP or HLOOKUP separately and is better because it can pull data back from either direction, not just one like HLOOKUP and VLOOKUP.

INDEX/MATCH/MATCH is a replacement for VLOOKUP and HLOOKUP together, and is perfect for lookup up data in a 2D table. Think of a table with Sizes down the left side, Brand names across the top of the table, and the inside of the table filled with prices. That's how you'd use INDEX/MATCH/MATCH to find the intersection of a specific size/brand to get the price.



Enough generalities. It's time to add a workbook to this thread showing me what you're trying to achieve.

Make sure there is just enough data to make it clear what is needed. Manually mockup the actual results you'd like to extract from that data and show it in the exact way you'd like to see it... show me the end goal and I'll see if I can insert a formula to do the same thing. Add several examples to make sure all the "variations" are covered in your sample results.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Soemd]

Auto-log_example.xlsx

Here is one I quickly setup with the relevant information. The comments say what the lot should be from the reference table. Let me know if it's enough!

[JBeaucaire]

Been staring at these tables for 10 minutes and not getting it at the moment. Rereading your question, though, wouldn't it be simpler in the long run to simply have two lookup tables side by side, one for R values and one for L values. You can check which one you need in an IF test, then run the formula needed on the correct table.

[Soemd]

...wouldn't it be simpler in the long run to simply have two lookup tables side by side, one for R values and one for L values. You can check which one you need in an IF test, then run the formula needed on the correct table.

Yeah! Actually that would work perfectly and it would make the lot list look less sparse for the final paperwork. I may have been too stuck in a do-while loop, thanks for the step out! Consider my issue solved