Lookup data from 2 separate columms

[jumonjii]

Hello-

I'm pretty new to Excel and am slowly chugging along with the help of this forum and Google.

I have run into a problem I haven't been able to find a solution to.

I am working on a workbook, it's not my workbook so I can't change how it's laid out.

I am trying to figure out a way that excel will check 2 different columns for a code that is manually entered, then enter the codes description in another cell.

It's basically a deposit and expense ledger.

One column has expense codes and names, the other deposit codes and names.

I want to be able to enter the code letter into a cell in the ledger and excel will enter the code description into another cell.

Thanks

[martindwilson]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

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

View Pic

[jumonjii]

I attached a blank sheet. I would like to be able to enter the code letter and the description would fill in automatically.

I was able to do it with vlookup using a single column, but can't figure out how to do it with 2 columns.

[azumi]

Something like this?

[jumonjii]

Something like this?

Rock on!

I don't know iferror yet and combining formulas....gads. I have a hard enough time trying to figure out what they are doing!

Thanks. Now to figure out how it works...

[azumi]

You're welcome......

[jumonjii]

Hello again. So I just found out that the users who will be accessing this workbook will be using Excel 2003. When I save it for Excel 2003 I get a message that there will be loss because the formula is not compatible.

[Tony Valko]

This one will work in any version of Excel.

=LOOKUP("zzz",CHOOSE({1,2,3},"",VLOOKUP(F11,$J$34:$K$44,2,0),VLOOKUP(F11,$N$34:$O$47,2,0)))

[jumonjii]

Wow! That's crazy.

I had to change it a bit but it works great!

Why does this work? LOL!

I like to understand the formula.

[Tony Valko]

The VLOOKUP functions will return either a TEXT value or a #N/A error.

So, for each code lookup we get 1 TEXT value and 1 #N/A error.

In this application we're using the LOOKUP function to return the last TEXT value from the array: {"",VLOOKUP1,VLOOKUP2}.

Let's assume VLOOKUP1 returns the #N/A error and VLOOKUP2 returns "Bank Errors".

LOOKUP("zzz",{"",#N/A,"Bank Errors"}) = Bank Errors

In Excel text has value just as numbers have value.

2 is greater than 1 and 3 is greater than 2.

B is greater than A and C is greater than B.

The way that LOOKUP works in this application is that it will return the LAST TEXT value that is less than or equal to "zzz".

"zzz" has a high value and the LAST TEXT value in the array {"",#N/A,"Bank Errors"} that is less than or equal to "zzz" is "Bank Errors".

If both VLOOKUPS return the #N/A error then the array is {"",#N/A,#N/A}. In this case the LAST TEXT value (which is also the only TEXT value!) is the "" blank.

[jumonjii]

I actually understand some of that...

Thanks a lot.

I'll have to go through my sheets now to make sure everything works with Excel 2003.

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.