Two criteria for Two ranges to return value from 3rd range

Phil Payne

Board Regular
Joined
May 17, 2013
Messages
131
Office Version
  1. 365
Platform
  1. Windows
Hello all,

I thought this would be straightforward but, no I keep getting "Not Found" when I can see a value?

Note the two columns of the GPR_Data worksheet B:B and F:F contain multiple instances of what I am looking for but if used together they will provide a unique reference.

I've tried these and more:
Excel Formula:
=if(and(C6=GPR_Data!B:B,D6=GPR_Data!F:F),GPR_Data!EZ:EZ,"not found")
and this
Excel Formula:
=if(and(GPR_Data!B:B=C6,GPR_Data!F:F=D6),GPR_Data!EZ:EZ,"not found")

Do I need to modify my formula or replace it?
Does it matter that the criteria are text and the result is currency?

I hope someone can guide me with this!

in english -
'If my,wksht.mycell.value (C6) can be found in other.wksht!columnB:B AND my,wksht.mycell.value (D6) is also to be found in other.wksht!columnF:F thenreturn the value found in other.wksht!column.EZ:EZ to mywksht.mycell (E6).
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
you are doing a lookup - so i dont think IF will help you

=Index(GPR_Data!EZ:EZ, match( C6&D6, GPR_Data!B:B&GPR_Data!F:F, 0))

then if not found add an IFERROR()

=IFERROR(Index(GPR_Data!EZ:EZ, match( C6&D6, GPR_Data!B:B&GPR_Data!F:F, 0)),"not found")

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

You can also test to see if it works ok, in the "Test Here" forum.

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone
 
Upvote 0
Solution
Thankyou Etaf,

You provided the solution - brilliant.

Thanks again.
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,126
Members
452,381
Latest member
Nova88

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