• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Look up formula

0

04_launder_spirals

New Member
Hello!

I have a column A with duplicate numbers and Column B has a different number assigned to each duplicate number. When I do a lookup. I get an incorrect match. Is there a formula that will return to the correct numbers in column B?
Thank you!

NumberParent numbervloop up
1234567​
4567890​
4567890
The formula returned duplicates
1234567​
4567891​
4567890
1234567​
4567892​
4567891
1234567​
4567893​
4567892
1234567​
4567894​
4567893
1234567​
4567895​
4567894
1234567​
4567896​
4567895
1234567​
4567897​
4567896
The formula didn’t return 4567897
 
04_launder_spirals said:
Hello!

I have a column A with duplicate numbers and Column B has a different number assigned to each duplicate number. When I do a lookup. I get an incorrect match. Is there a formula that will return to the correct numbers in column B?
Thank you!

NumberParent numbervloop up
1234567​
4567890​
​
4567890
The formula returned duplicates
1234567​
4567891​
4567890
1234567​
4567892​
4567891
1234567​
4567893​
4567892
1234567​
4567894​
4567893
1234567​
4567895​
4567894
1234567​
4567896​
4567895
1234567​
​
4567897​
4567896
The formula didn’t return 4567897
Click to expand...
Hello launder

I can think of this based on your data...May be need more details to support you.If you want to return the correct corresponding numbers in Column B when there are duplicates in Column A, you can use the `INDEX` and `MATCH` functions. Assuming your data is in columns A and B as you have not mentioned, if you want to look up the corresponding value in B for a given value in A, you can use the following formula:

Assuming you are looking up the value in cell C1 (replace it with your actual lookup value):

Code: 
=INDEX($B$1:$B$100, MATCH(C1, $A$1:$A$100, 0))

This formula searches for the value in cell C1 within the range A1:A100 and returns the corresponding value from B1:B100.

Make sure to adjust the cell references based on the actual range of your data. If you have duplicate values in Column A, this formula should return the correct corresponding value from Column B.All the Best!
 
Monty said:
Hello launder

I can think of this based on your data...May be need more details to support you.If you want to return the correct corresponding numbers in Column B when there are duplicates in Column A, you can use the `INDEX` and `MATCH` functions. Assuming your data is in columns A and B as you have not mentioned, if you want to look up the corresponding value in B for a given value in A, you can use the following formula:

Assuming you are looking up the value in cell C1 (replace it with your actual lookup value):

Code: 
=INDEX($B$1:$B$100, MATCH(C1, $A$1:$A$100, 0))

This formula searches for the value in cell C1 within the range A1:A100 and returns the corresponding value from B1:B100.

Make sure to adjust the cell references based on the actual range of your data. If you have duplicate values in Column A, this formula should return the correct corresponding value from Column B.All the Best!
Click to expand...
Thank you Monty!
 
I do not understand the question. If you use a lookup formula (though VLOOKUP is obsolete) to look a value up in an array it will produce the same result every time.
Code: 
= XLOOKUP(value, number, parent)
Given you have multiple occurrences, it is also possible to use FILTER (using Excel 365 or 2021)
Code: 
= FILTER(parent, number=value)
to return the list of matching parent numbers.
1702553960828.png
What is the logic that determines which single 'parent value' to return.
 
You must log in or register to reply here.
Back
Top