VLOOKUP - Negative / Reverse Column Reference - Search "B" and return data from "A"

[Askjerry]

I program radios... some take only 8 characters, some 12, others 14, and some 20. To keep things simple we decided to only allow two options... 12 Characters and 8 Characters. So if the radio has 11 characters allowed... use the 8 character name.

We set up a (huge) spreadsheet with all the groups for the whole region something like this...

"MASTER" tab (worksheet)
COL A = 12 Character Name
COL B = 8 Character Name
COL C = Digital Code
COL D to M = a bunch of other stuff we don't need for this example.

12 CHAR Name | 8 Character Name | Digital Code | Other stuff....
------------------------------------------------------------------
North Fire N-Fire 17 | stuff...
South Fire S-Fire 26 | stuff...
North Patrol N-Patrol 18 | stuff...
South Patrol S-POL 35 | stuff...

So it was easy to do a VLOOKUP to enter the 12 character name and find the 8 character name from the MASTER tab...

I'm using cell "C3" as the reference... but in reality there are 50 search cells and 50 return cells...

=VLOOKUP(C3,MASTER!A:L,2,FALSE)

This formula is populated in the search column... and I can paste in 1 to 50 of the 12 CHAR names... poof... I have all my 8 CHAR counterparts that I can copy-paste into the software... nice... time saving.

But what if I want to make another section to enter the 8 Character name... and return the 12 Character name... in the column BEFORE the search column???

I tried something like...

=VLOOKUP(C3,MASTER!B:L,-1,FALSE)

But I just get #VALUE as a result... Excel doesn't like it.

So... without me needing to duplicate the entire "A" column to the right of the "B" column... how do I do this?

I.E. Search column "B" for a match in column "A" instead of the other way around?

Is there another field in the VLOOKUP that can say: "The table spans A to H, but you are going to search B and return A."

Any help would be greatly appreciated... in a given day I'm looking up 500 to 2000 items.

Thanks,
Jerry

 

[Jonmo1]

Welcome to the baord..

You need Index Match to do a lookup to the left..

=INDEX(Columntoreturnvaluefrom,MATCH(Valuetofind,columntofindvaluein,0))

So to find the match for D1 in Column B, and return a value from A
=INDEX(A:A,MATCH(D1,B:B,0))

Hope that helps.

 

[Askjerry]

Okay... I need to wrap my head around this... I'm close... but perhaps my caffeine level is a tad low...

The TAB with all the data is called "MASTER" and I had the presence-of-mind to actually name the columns so that I could use validation, etc.

The Column with the 12-Character names is "M_12_CHAR" and the Column with the 8-Character names is "M_8_CHAR" so I think my references would be...

MASTER!M_12_CHAR and MASTER!M_8_CHAR for the formula.

So let me see...

=INDEX( MASTER!M_8_CHAR , MATCH( mycell , MASTER!M_12_CHAR , 0 ))

So everything stays the same... but "mycell" changes to be the current cell I'm searching...

Like ...
=INDEX( MASTER!M_8_CHAR , MATCH( C5 , MASTER!M_12_CHAR , 0 ))
=INDEX( MASTER!M_8_CHAR , MATCH( C6 , MASTER!M_12_CHAR , 0 ))
=INDEX( MASTER!M_8_CHAR , MATCH( C7 , MASTER!M_12_CHAR , 0 ))
...etc...
...etc...

Hey... IT WORKS!!! Awesome! (I owe you a lunch!)

Funny thing... after I hit ENTER the formula changes to...

=INDEX('MY SPREADSHEET NAME.xlsm'!M_12_CHAR,MATCH(H3,'MY SPREADSHEET NAME.xlsm'!M_8_CHAR,0))

I named the entire MASTER data sheet tab MASTER_ALL... let me see if it allows a named reference instead of a spreadsheet name...

=INDEX(MASTER_ALL!M_12_CHAR,MATCH(H3,MASTER_ALL!M_8_CHAR,0))

Nope... odd... tries to point to a file. Guess I just need to leave it alone and back away... it's working so... party time!

Thanks again!!!
Jerry

 

[Jonmo1]

Great, glad to help. Thanks for the feedback.