Lookup question

[mdupontm]

Hi am trying to figure out a way to use a look up function that would search through the second column as a reference point and not the first. how do I do this ?

thanks

[Ron Coderre]

An example would be helpful.
Absent that....Here's one approach:

This formula finds the C1 value in col_B and return the corresponding
value from col_A

=INDEX(A1:A10,MATCH(C1,B1:B10,0))

Does that help?

[mdupontm]

If I have the following table:

LOCATION CAT DESC WID DEP POS HGT POS HGT X Y Z
@0420001 R TP 48 40 0 0 *** 288 13393 21603 0
@0540001 R TP 48 40 0 0 *** 288 11633 10218 0
@0620001 R TP 48 40 0 0 *** 288 14432 21603 0
@0620002 R TP 48 40 0 0 *** 288 14432 21603 0
@0870001 R TP 48 40 0 0 *** 288 14425 10218 0
@1370004 R TP 48 40 0 0 *** 288 15140 10218 0
@1540003 R TP 48 40 0 0 *** 288 13330 10218 0
@1620001 R TP 48 40 0 0 *** 288 15532 10218 0
@1820005 R TP 48 40 0 0 *** 288 11991 10218 0
@2140010 R TP 48 40 0 0 *** 288 12590 10218 0
@2750001 R TP 48 40 0 0 *** 288 12590 10218 0
@2850001 R TP 48 40 0 0 *** 288 16659 10218 0


I have a separate table that needs to display the value in column A (Location) if column B is displayed as "R". vlookup would work but it only references the first column?!

[Ron Coderre]

Still a bit short on requirements...but, working with what you gave us....

With your data in A1:L13

This formula returns the Col_A value if the Col_B value is "R"

M2: =IF(B2="R",A2,"")

Copy that formula down through M13

Does that help?

[mewingkitty]

Just wondering why this won't work...
Switch the columns?

Why not just open your spreadsheet, move the second column so it's in front of the first one, and use vlookup?

[mdupontm]

This sort of works in that if I use this, all the rows that don't have "R" in column B are blank and so I am left with huge gaps in my table.

[Ron Coderre]

Now that you've posted a sampling of your data, we need to see what the
final results should be. More detail is better than less.

[mdupontm]

If I use this as the original table:

LOCATION CAT DESC WID DEP POS HGT POS HGT X Y Z
01-2472 S SF 101 61 1 60 0 0 10092 11631 26
01-2473 R SF 234 61 1 60 0 0 10092 11646 26
01-2474 R SF 122 61 1 60 0 0 10092 11662 26
01-2475 S SF 121 61 1 60 0 0 10092 11677 26
01-2476 S SF 120 61 1 60 0 0 10092 11692 26
01-2477 S SF 102 61 1 60 0 0 10092 11707 26
01-2478 S SF 122 61 1 60 0 0 10092 11723 26
01-2479 R SF 103 61 1 60 0 0 10092 11738 26
01-2611 R SF 122 102 200 183 0 0 10092 11753 26
01-2612 R SF 122 61 1 60 0 0 10092 11768 26
01-2613 S SF 62 61 1 60 0 0 10092 11783 26
01-2614 S SF 102 61 1 60 0 0 10092 11799 26
01-2615 R SF 61 61 1 60 0 0 10092 11814 26
01-2616 S SF 109 61 1 60 0 0 10092 11829 26
01-2617 R SF 102 61 1 60 0 0 10092 11844 26
01-2618 S SF 122 61 1 60 0 0 10092 11860 26
01-2631 S SF 53 61 1 60 0 0 10092 11875 26
01-2632 R SF 102 61 1 60 0 0 10092 11890 26
01-2633 S SF 122 61 1 60 0 0 10092 11905 26
01-2634 R SF 55 61 1 60 0 0 10092 11921 26
01-2635 R SF 53 61 1 60 0 0 10092 11936 26
01-2636 S SF 122 61 1 60 0 0 10092 11951 26
01-2637 S SF 217 61 1 60 0 0 10092 11966 26
01-2638 S SF 51 61 1 60 0 0 10092 11982 26


This is what I get as the result (see the spaces in between):

LOCATION CAT DESC X Y Z

01-2473
01-2474




01-2479
01-2611
01-2612


01-2615

01-2617


01-2632

01-2634
01-2635




I would like to get rid of these spaces, either by using a different function or by adding another one.
Keep in mind that I am only applying this formula to the first column right now and will modify it for the rest

[mdupontm]

Anyone have any ideas ? I spent last night trying to figure this out but could not come up with anything