VLookup Issue with returning value if multiple

[ronie85]

Hi, I am hoping someone can rewrite my Vlookup formula.

The formula is currently =IFERROR(VLOOKUP(B176,'Material Purchasing'!$B$2:$E$999999,4,FALSE),"")
And the purpose of the formula is to search for a PO Number (B176) within the range of Material Purchasing'!B2:B999999 and return a value Material Purchasing'!E2:E999999, which it does.
However, this range Material Purchasing'!E2:E999999 may contain more than 1 value for which I would prefer the original =IFERROR(VLOOKUP(B176,'Material Purchasing'!$B$2:$E$999999,4,FALSE),"") formula to show an error for which I can then check it manually.

Would someone know of an easy fix for which it will only return a value if Material Purchasing'!E2:E999999 only has one output and not multiple?

 

[etaf]

add a countif() within an IF

=IFERROR(IF(COUNTIF(Purchasing!E2:E999999,B176)>1,"Error",VLOOKUP(B176,'Material Purchasing'!$B$2:$E$999999,4,FALSE)),"")

 

[ronie85]

add a countif() within an IF

=IFERROR(IF(COUNTIF(Purchasing!E2:E999999,B176)>1,"Error",VLOOKUP(B176,'Material Purchasing'!$B$2:$E$999999,4,FALSE)),"")

Thanks, I assume the formula is the below, however it is showing FALSE for ranges that don't have more than 1 return value

=IFERROR(IF(COUNTIF('Material Purchasing'!$B$2:$B$999999,B176)>1,"Error",VLOOKUP(B176,'Material Purchasing'!$B$2:$E$999999,4,FALSE)),"")

 

[etaf]

ok,
IF(COUNTIF('Material Purchasing'!$B$2:$B$999999,B176)>1,"Error",VLOOKUP(B176,'Material Purchasing'!$B$2:$E$999999,4,FALSE))
Should if the value is 1 or 0 , then it will do the lookup
If 1 then it will do the lookup and will return whatever value
and if 0 - then it will still do the lookup - but return an error N/A

what does the IF formula return

=IF(COUNTIF('Material Purchasing'!$B$2:$B$999999,B176)>1,"Error",VLOOKUP(B176,'Material Purchasing'!$B$2:$E$999999,4,FALSE))

 

[ronie85]

ok,
IF(COUNTIF('Material Purchasing'!$B$2:$B$999999,B176)>1,"Error",VLOOKUP(B176,'Material Purchasing'!$B$2:$E$999999,4,FALSE))
Should if the value is 1 or 0 , then it will do the lookup
If 1 then it will do the lookup and will return whatever value
and if 0 - then it will still do the lookup - but return an error N/A

what does the IF formula return

=IF(COUNTIF('Material Purchasing'!$B$2:$B$999999,B176)>1,"Error",VLOOKUP(B176,'Material Purchasing'!$B$2:$E$999999,4,FALSE))

I have tried 0 and it gives erors for all rows

I tried 1, it returns a value and errors on different rows, however it is inconsistent

I have found what the error is, using 1 will return a value when there is only 1 result within the B column however the B column can have 1 result or 100 results.
What I require (I should have been more specific) is that if B has 1 or 100 results, it needs to match the correcponding result in column E and only return the value if column E completly matches column B. If E has 1 consistency away from B then it should return an Error.

 

[etaf]

i'm not following now whats needed

using 1 will return a value when there is only 1 result within the B column

, YEP thats what i thought was needed

however the B column can have 1 result or 100 results.

YEP , thats when ERROR returns

is that if B has 1 or 100 results, it needs to match the correcponding result in column E

VLOOKUP will ONLY return the FIRST value in B it finds

If E has 1 consistency away from B then it should return an Error.

DONT understand that


this is how i understand he requirement and what the formula does


the =COUNTIF('Material Purchasing'!$B$2:$B$999999,B176)>1
is looking in the range in column B and counting how many times B176 exists in that column
If it counts that B176 exist 2 or more times (hence >1 ) - then TRUE and so returns the word ERROR

If its counts 1 , FALSE

then it will use the lookup
,VLOOKUP(B176,'Material Purchasing'!$B$2:$E$999999,4,FALSE)
and lookup B176 and return the value in column 4 along from B = column E
if it counts 0 , FALSE
then it will STILL use the lookup
,VLOOKUP(B176,'Material Purchasing'!$B$2:$E$999999,4,FALSE)
BUT that will return a N/A error as B176 does NOT exist and so error
hence the
IFERROR to catch that condition and return nothing ,"")

 

[ronie85]

i'm not following now whats needed

, YEP thats what i thought was needed

YEP , thats when ERROR returns

VLOOKUP will ONLY return the FIRST value in B it finds

DONT understand that


this is how i understand he requirement and what the formula does


the =COUNTIF('Material Purchasing'!$B$2:$B$999999,B176)>1
is looking in the range in column B and counting how many times B176 exists in that column
If it counts that B176 exist 2 or more times (hence >1 ) - then TRUE and so returns the word ERROR

If its counts 1 , FALSE

then it will use the lookup
,VLOOKUP(B176,'Material Purchasing'!$B$2:$E$999999,4,FALSE)
and lookup B176 and return the value in column 4 along from B = column E
if it counts 0 , FALSE
then it will STILL use the lookup
,VLOOKUP(B176,'Material Purchasing'!$B$2:$E$999999,4,FALSE)
BUT that will return a N/A error as B176 does NOT exist and so error
hence the
IFERROR to catch that condition and return nothing ,"")

Possibly it is not a Vlookup I require

When B176 is searched in Material Purchasing'!$B$2:$B$999999, B176 may appear 10 times. The values in Material Purchasing'!$E$2:$E$999999 may all be the same for which I need it to return the first value shown within Material Purchasing'!$E$2:$E$999999, which the formula does.

However, if Material Purchasing'!$E$2:$E$999999 contains 1 or more different values, I need the formula result to show NA or Error, so that I know i need to manually look at the results.

Would you know a formula that can do this which may not be a Vlookup?

 

[Joe4]

etaf solution should do exactly what you want - it is first counting the matches, and if there are more than one, it is returning your desired text.
If there is exactly one, it is looking up that value via a VLOOKUP.
If there are none, it will return an empty string.

Are you applying his formula exactly as he wrote it, or are you making changes/adaptions to it?
If so, please post the formula, as you have edited it.

If you have not changed anything, you may have a a data issue.
Please create a small example that shows it not working correctly, as you want it to.
Please be sure to show the data and formula you are using in that example so we can recreate it here on our side.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ronie85]

etaf solution should do exactly what you want - it is first counting the matches, and if there are more than one, it is returning your desired text.
If there is exactly one, it is looking up that value via a VLOOKUP.
If there are none, it will return an empty string.

Are you applying his formula exactly as he wrote it, or are you making changes/adaptions to it?
If so, please post the formula, as you have edited it.

If you have not changed anything, you may have a a data issue.
Please create a small example that shows it not working correctly, as you want it to.
Please be sure to show the data and formula you are using in that example so we can recreate it here on our side.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I'll post what i require and then try the above

Green cell I176 within the Supplier Payments tab is where the formula will be
This formula searches B176 which is PO-10110

PO-10110 will search column B within the Material Purchasing tab for which it will see the range of 316 through to 320 shown in yellow
It will then check the same range in column E shown in green and orange, however as orange is a different value the the green, i would expect it to return NA or error.
If all 5 cells within the 316 to 320 range were the same (JN10089), i need it to return the value of JN10089

Please note that I have set all ranges for columns as 2:999999 to future proof it.

 

[Joe4]

OK, now that we can see the data, I think the issue is you did not explain your situation and desired results clearly.

When you said there could be multiple different matching records on the Material Purchasing tab, you did not clearly state that there could be multiple records with the same value (duplicates) that you do NOT consider multiples (i.e. row 316-319). I believe that etaf and I were under the impression that if you found more than one record on the matching column B on the Materials Purchasing tab, that would be considered multiple records, regardless of what appears in column E.

That is a big trickier and would require a different formula, to count the number of unique values. I am not sure how to do that, off the top of my head. I would have to play around with it and see if I could come up with something.