match and equals in seperate sheets

Copper Contributor



I have 2 sheets.

In the main sheet I have a column with ordernumbers and one blanc column that i want the invoice number in.

In the other sheet I have the ordernumber and in the next column I have the invoice number.


So I want the blanc column(invoice number column) in the main sheet, to be filled with the invoicenumbers that equals to the ordernumbers thats next to it. Is this possible?? 


5 Replies

@nickie115 If you have only one invoice number per order in the "other sheet" you can use VLOOKUP or XLOOKUP (if your Excel version supports it. If, on the other hand, you have multiple invoice numbers for the same order, you'll need something more sophisticated.

I have one invoice number per order but i have multiple orders in the main sheet. I'm kind of lost what columns i should mark as what if I use VLOOKUP or XLOOKUP

@nickie115 Perhaps the attached file will help,

Thank you but what do you write after vlookup?

What is our search key? is it our invoicenumber or ordernumber?

Which range is it? Is it the invoice number/ordernumber?

What is the index and how is it sorted?

VLOOKUP(search_key, range, index, [is_sorted])

@nickie115 Have you looked at the formulae in my example? Google for VLOOKUP and/or XLOOKUP. There are many tutorials on line. Better at explaining than what I can write up here in a few minutes.