Need help with LOOKUP or INDEX/MATCH formula

[HeyInKy]

I have used VLOOKUP and even INDEX/MATCH successfully before, but every new worksheet presents a little variation - and my project today is throwing me off just a bit...

I have 6 columns of data in A - F. Column B (about 3,000 rows) contains a 3-digit numeric code. There are 100 different codes (from 001 thru 100), each with a different description, but the description is not in the data set. So I inserted my 'key' in columns J2:K101, with column J (J2:J101) containing the text description, and column K (K2:K101) with the 3-digit numeric codes (in order 001-100, the codes in column B are not sorted in order.).

So my formula in my next column (G), needs to look at the code in column B, find it in column K, and return the value in column J. Should be easy enough - but it's giving me fits.

Sorry, no workbook - because I think this is fairly straight forward - but if anyone wants to help and really needs me to upload an example, let me know. Thanks!

-HeyInKy

[nathansav]

It will be index and match

=INDEX($A$1:$F$3000,match($B1,$K$1:$K$3000,0),10)

Not tested.

[ChemistB]

If you put the code in J and the description in K, you could have used VLOOKUP
=VLOOKUP(B2, $J$2:$K$101,2, FALSE)

with the code in K
=INDEX($K$2:$K$101, MATCH(B2, $J$2:$J$101,0))
or
=LOOKUP(B2, $K$2:$K$101, $J$2:$J$101)
Does that help?

[ben_hensel]

Please Login or Register  to view this content.

Like that?

Find the position in column K of what matches the entry in the same row of B column, and then return what's sitting in J column into the G row.

[Fotis1991]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[HeyInKy]

Thanks all for your suggestions and help. Just FYI, nathansav's returned #N/A, as did ChemistB's INDEX array... however the simple LOOKUP formula worked great, as did ben_hensel's INDEX array formula. Thanks again!

-HeyInKy

[ChemistB]

Yes, put the formula in backwards

with the code in K
=INDEX($K$2:$K$101, MATCH(B2, $J$2:$J$101,0))
should be
=INDEX($J$2:$J$101, MATCH(B2, $K$2:$K$101,0))

[HeyInKy]

Ok... this is weird and you may not have an explanation. The formulas worked fine. But I had it on sheet1. I have duplicate-formatted data on sheet3, and decided to move my key to there as well. So my formula on sheet1 is:

=INDEX(Sheet3!$J$2:$J$121,MATCH(B2,Sheet3!$K$2:$K$121,0))

Works great. But when I try to enter either of the formulas in column G on sheet3, I get #N/A (Ref error)... for the life of me I can't figure out why it's doing that. The data in column B is identical (in format) to column B on sheet1. Without me uploading the workbook (I would have a lot of data to sanitize), what could be causing this? Both formulas are pointing to the same key!

[ChemistB]

Where is the B2, you are trying to match against? If it's on sheet1, then you need to add that to the formula when putting it into Col G of Sheet3

[HeyInKy]

I've discovered the problem...column B on Sheet3 is NOT formatted the same. But excel is not recognizing it as a number even though it's formatted as a number. So I'll figure it out. Thanks again!!