Getting a Given Number from Either of Two Columns

**vito01** · 02-14-2019, 08:38 AM

Hi Guys,

I wondered if you can help me pls

I need to look for a given number in a set of data but the result I want to get can be in either of two columns:

FROM
Document No_ A B
ST1986874 TAXABLE eu services
ST1986870* eu goods TAXABLE
ST1986878 TAXABLE TAXABLE
ST1986871 TAXABLE eu services
ST1986870* eu goods TAXABLE

TO
Document No_ A/B
ST1986874 to bring the result into
ST1986870
ST1986878
ST1986871
ST1986870

Note, sometimes the same number may appear twice

Thanks

Vito

**AliGW** · 02-14-2019, 09:40 AM

Please make sure your thread titles are more descriptive. I have updated this one for you this time.

**vito01** · 02-14-2019, 10:01 AM

Thank You!

**GeoffW283** · 02-17-2019, 09:20 PM

Looking at your "From" and "To" data it looks as simple as the following (assuming your data starts in A2).

Formula:

Please Login or Register  to view this content.

I suspect however that it is not this simple. You mention that the result you want can be in either of 2 columns, but your sample data does not reflect that.

Please attach a workbook with sufficient sample data to cover all necessary cases along with your expected results for each.

To attach a file, first make sure to remove any sensitive/proprietary data and then click on “GO ADVANCED” and then scroll down to “Manage Attachments” to open the upload window. Choose your file then click on “Upload”, scroll down then click on “Close this window”, then “Submit reply”.

Thanks

Geoff

**vito01** · 02-18-2019, 05:36 AM

Hi,

Thanks for coming back to me

Please see attached as requested

Thanks

Vito

**AliGW** · 02-18-2019, 05:41 AM

Try this in B10 copied down:

=IF(AND(VLOOKUP(TRIM(A10),$A$2:$C$6,2,0)="TAXABLE",VLOOKUP(TRIM(A10),$A$2:$C$6,3,0)="TAXABLE"),"TAXABLE",SUBSTITUTE(VLOOKUP(TRIM(A10),$A$2:$C$6,2,0)&VLOOKUP(TRIM(A10),$A$2:$C$6,3,0),"TAXABLE",""))

**vito01** · 02-18-2019, 05:51 AM

Thank you, it works like a dream!

Cheers

Vito

**AliGW** · 02-18-2019, 05:52 AM

You're welcome!

I had to use TRIM as the lookup values have trailing spaces.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Getting a Given Number from Either of Two Columns

LinkBack

Thread Tools

Rate This Thread

Display

Getting a Given Number from Either of Two Columns

Re: Getting a Given Number from Either of Two Columns

Re: Getting a Given Number from Either of Two Columns

Re: Getting a Given Number from Either of Two Columns

Re: Getting a Given Number from Either of Two Columns

Re: Getting a Given Number from Either of Two Columns

Re: Getting a Given Number from Either of Two Columns

Re: Getting a Given Number from Either of Two Columns

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] INDEX+MATCH instead of VLOOKUP+MATCH, why is INDEX a better choice and how to re-write?

[SOLVED] Index / Match - match 3 input values and return the results from the index

[SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays

INDEX MATCH MATCH/OFFSET MATCH MATCH with named ranges

Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY fail

Index, match, dynamic and connected without sorting helper column non-adjacent index match

[SOLVED] Concatenated INDEX MATCH formula that won't give error that can index match 2-4 strings

Bookmarks

Bookmarks

Posting Permissions