Help! Vlookup, Index Match match, sumproduct? Matching text with multiple conditions

Probably you get better help, if you add an excel file, without confidentional information.

Please also add the desired result.

you have a 2nd set of "headings" 1/2 way down your data, and none of teh item-no's match anything on the newsheet. what is your expectation for that?.

as suggested, upload a sample work book, (all sensitive data removed), showing what data you are working with, what your expected outcome is, and how you arrived at that

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

chicolocal,

An index/match/match should work just fine for that. I know you said you tried it already, but can you upload an example workbook so we can see what's going on?

[EDIT]: I really need to refresh the page before I post, lol

take your pick...

=VLOOKUP($A2,oldsheet!$A$2:$M$19,COLUMN(B1),FALSE)
=INDEX(oldsheet!$A$1:$M$19,MATCH(newsheet!$A2,oldsheet!$A$1:$A$19,0),MATCH(newsheet!B$1,oldsheet!$A$1:$M$1,0))

lol not really, but thanks though. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neet and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)

Originally Posted by FDibbins

take your pick...

=VLOOKUP($A2,oldsheet!$A$2:$M$19,COLUMN(B1),FALSE)
=INDEX(oldsheet!$A$1:$M$19,MATCH(newsheet!$A2,oldsheet!$A$1:$A$19,0),MATCH(newsheet!B$1,oldsheet!$A$1:$M$1,0))

FDibbins, thanks again for your reply. I have attached an additional file using the formulas you suggested. The formulas are highlighted in yellow on the 'newsheet' table. Unfortunately it isn't working how I need it to, notice how on the oldsheet table, the spec titles are not all in the same column. For example column M on 'oldsheet' has both headstroke and piston stroke titles. As I fill down the formula, on my newsheet, it pulls the wrong data from the old sheet as it is referencing the column itself, not the intersection point where the item number matches the column containing the text "headscrew".

As far as I can tell the formula I am looking for might incorporate the sumproduct and or offset formulas, maybe a vlookup/match...

Thanks again for your help!

you essentially have 4 different tables there, and I am assuming that you have far more rows in each 1 than you have in the sample you provided?

having different headings in different places is not a problem if you use the index/match option - it matches headings no matter what order they are in

however, having different headings in the different tables IS a problem, because the index/match is based on the very 1st row of headings. is there a way that you can add spaces in the other tables, then add the extra headings to the 1st row so that the data matches up downwards? (if that made sense lol). so in other words can you change I17 to just "capacity", or move that data to N17, move "Max Handle Effort" data to O etc?

if you can do that, the index/match will work. failing that, maybe we need to look at using named ranges instead, but i dont know how that will work if your data ranges will be increasing over time

awesome, glad it worked out for you, and happy to help - thanks for the rep too