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.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
Try this:
Code:
=IF(COUNTA(C:C)<>0,MATCH(B2,'Res Gps'!C:C,0),MATCH(B2,'Res Gps'!A:A,0))
 

jmomich

New Member
Joined
Sep 24, 2014
Messages
4
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?
 

bbott

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
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))
 

jmomich

New Member
Joined
Sep 24, 2014
Messages
4
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,324
Messages
5,600,956
Members
414,417
Latest member
Nobu

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
Top