Help requested with ISBLANK and nested MATCH formula

jmomich

New Member
Joined
Sep 24, 2014
Messages
4
Hello - I am new to the board and wondering if somebody could help me find the correct syntax. I am using Win 7 and Excel 2010. I have the following formula, =IF(ISNA(MATCH(B2,'Res Gps'!A:A,0)),"No","Yes"), that takes a look at cell B2, then goes to another worksheet 'Res Gps' in the same workbook to see if it finds a match in the A column. If it finds a match, it returns a Yes value, if not, it returns a No value.

I need this formula to look in column C first and determine if that particular column is empty, that's why I was trying to use the ISBLANK function. If column C is populated, I need it to be able to make the match from the 'Res Gps' worksheet from the C column. If column C is empty, I need it to be able to make the match from the 'Res Gps' worksheet from the B column.

Sorry for rambling on, hope this makes sense. Thanks for any help offered.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Try this:
Code:
=IF(COUNTA(C:C)<>0,MATCH(B2,'Res Gps'!C:C,0),MATCH(B2,'Res Gps'!A:A,0))
 
Upvote 0
Hello Bruce - thanks for the help. Unfortunately this did not yield the result I need. I might be misunderstanding, but I don't need to count something in this case.

I took a closer look at my data and think this might be able to be accomplished a different way. I can have the formula look at column C, and if that is empty then just use column B instead -- keeping in mind of course that I need to match that value to column A in the 'Res Gps' worksheet. Does that make sense?
 
Upvote 0
To amend Skywriter's formula, try this. The COUNTA is used to see if C is empty. ISBLANK will only look at a singe cell.

=IF(COUNTA('Res Gps'!C:C)<>0,MATCH(B2,'Res Gps'!C:C,0),MATCH(B2,'Res Gps'!A:A,0))
 
Upvote 0
Hi Guys - you put me on the right track, and I got this figured out over the weekend. This is what I used that gave me the desired results:

=IF(B2="",IF(ISNA(MATCH(A2,'Res Gps'!A:A,0)),"No","Yes"),IF(ISNA(MATCH(A2,'Res Gps'!A:A,0)),"No","Yes")).

Sorry if I wasn't communicating very well. I appreciate your feedback!
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top