Return one value if data exists, another if data doesn't exist

**dawsonsoo** · 01-20-2015, 07:56 AM

Hi,
I'm hoping someone can help.

I have a spreadsheet "A" with 5-digit numbers in column A and there are over 10K rows.

Then I have a spreadsheet "B" with 5-digit numbers in column A and there are only 223 rows.

IF a value in spreadsheet A column A matches spreadsheet B column A, I'd like spreadsheet B column B to log "U". If not, I'd like spreadsheet B column B to log "R." At the end of the day, I'd like all 223 rows in spreadsheet B column B to have either "U" or "R" assigned. Could someone please help?

Thanks!
~S

**rabbita** · 01-20-2015, 08:09 AM

Hello,

Please loot at the solution I attached in a file below:

s1.xlsx

I used functions: VLOOKUP, IF and ISERROR.

Regards,
Alex

**TMS** · 01-20-2015, 08:10 AM

You can use COUNTIF to check if a number in sheet B exists in sheet A. And then you can use a simple IF function to flag U or R.

Maybe post a sample workbook ... or are there two workbooks?

Regards, TMS

**dawsonsoo** · 01-20-2015, 08:16 AM

TMS, thanks so much!

Here's are the two workbooks - please advise.

Best,
~S

**TMS** · 01-20-2015, 08:23 AM

Assuming workbook A is open, then, cell B2:

Formula:

Please Login or Register  to view this content.

Regards, TMS

**TMS** · 01-20-2015, 08:25 AM

Should note that if workbook A is closed, you'll get #VALUE! errors

**TMS** · 01-20-2015, 08:44 AM

You can use this:

Formula:

Please Login or Register  to view this content.

or this:

Formula:

Please Login or Register  to view this content.

with a closed workbook.

Change the username as required.

Note that it is MUCH better to have a defined range rather than searching the whole column.

Regards, TMS

**dawsonsoo** · 01-20-2015, 08:47 AM

Excellent tip, TMS!!!
Thanks so very much :-)
~S

**TMS** · 01-20-2015, 08:49 AM

You're welcome. Thanks for the rep.

Return one value if data exists, another if data doesn't exist

LinkBack

Thread Tools

Rate This Thread

Display

Return one value if data exists, another if data doesn't exist

Re: Return one value if data exists, another if data doesn't exist

Re: Return one value if data exists, another if data doesn't exist

Re: Return one value if data exists, another if data doesn't exist

Re: Return one value if data exists, another if data doesn't exist

Re: Return one value if data exists, another if data doesn't exist

Re: Return one value if data exists, another if data doesn't exist

Re: Return one value if data exists, another if data doesn't exist

Re: Return one value if data exists, another if data doesn't exist

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Code to paste form data to next available row if cb1 value doesn't exist in column a

[SOLVED] Counting Data Which Doesn't Exist..

Lsit From Data Validation References Sheet That Doesn't Exist!

Don't know how to display data it in pivot table that doesn't exist in the org source but

Cell References data that doesn't exist?

Bookmarks

Bookmarks

Posting Permissions